Solved

duplicate data not insert in mysql

Posted on 2016-10-04
46
77 Views
Last Modified: 2016-10-04
Hi,
i have 4000 record in a table. i want duplicate value not insert in table. i use ignore or replace but its insert data.
INSERT INTO  `projects` (`id` ,`projects`, `created` ) VALUES ( '', 'PHP', '' ) WHERE NOT EXISTS (SELECT *
                  FROM projects
                  WHERE projects = 'PHP')

Open in new window

0
Comment
Question by:Amita Singh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 12
  • 10
  • +2
46 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41827541
Try this , Seems like NULL issue

AND projects IS NOT NULL


INSERT INTO  'projects' ('id' ,'projects', 'created' ) VALUES ( '', 'PHP', '' ) 
WHERE NOT EXISTS (SELECT *
                  FROM projects
                  WHERE projects = 'PHP' AND projects IS NOT NULL)

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41827555
Are you just wanting to insert one row when it doesn't exist?
0
 
LVL 2

Author Comment

by:Amita Singh
ID: 41827557
yes. if data exit its skip insert or data not exit its insert.
0
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41827563
Please try this !!

INSERT INTO  'projects' ('id' ,'projects', 'created' ) 
SELECT '', 'PHP', '' FROM Dual
WHERE NOT EXISTS (SELECT *
                  FROM projects
                  WHERE projects = 'PHP')

Open in new window

0
 
LVL 2

Author Comment

by:Amita Singh
ID: 41827564
sql show error when i run query

11.png
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41827566
Please try the last one..
0
 
LVL 2

Author Comment

by:Amita Singh
ID: 41827570
same error..

12.png
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41827576
Actually on my machine it looks fine. May be try below....

INSERT INTO projects (id ,projects, created)
SELECT '', 'PHP', '' FROM Dual
WHERE NOT EXISTS (SELECT *
                  FROM projects
                  WHERE projects = 'PHP')
0
 
LVL 2

Author Comment

by:Amita Singh
ID: 41827579
i m facing same problem.
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 41827580
What do you need to do?
Are you trying to insert a row with  just the value 'PHP' in field projects as many times as  it already exists?
0
 
LVL 2

Author Comment

by:Amita Singh
ID: 41827583
in a table 4k records when i run my sql query if data exit in table its skip insert and if data not exit in table its insert.

i want skip duplicate entry.
0
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 41827586
Make projects a unique key.
Then

INSERT INTO  `projects` (`id` ,`projects`, `created` ) VALUES ( '', 'PHP', '' )
   ON DUPLICATE KEY UPDATE projects = projects;

Open in new window

0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41827588
Try this..

INSERT IGNORE INTO projects (id, projects,created) VALUES ( '', 'PHP', '' )
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 41827589
But wich data? You're inserting a row with just 'PHP' in field project. When must this be done? When another record with 'PHP' is not found? And what about indexes (is that ID field a Key)?
0
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 41827590
Looking at your query I am going to assume that id is an AUTONUMBER and created is a time stamp in  which case you probably want this
INSERT INTO  `projects` (`projects` ) VALUES ('PHP' )
   ON DUPLICATE KEY UPDATE projects = projects;

Open in new window

0
 
LVL 35

Assisted Solution

by:Terry Woods
Terry Woods earned 125 total points
ID: 41827593
Making it a unique key and using INSERT IGNORE is definitely easier, but if you can't or don't want to, you can still do this:

INSERT INTO projects (id ,projects, created) 
SELECT distinct '', 'PHP', '' FROM projects
WHERE NOT EXISTS (SELECT *
                  FROM projects
                  WHERE projects = 'PHP')

Open in new window


with one requirement: the projects table can't be empty to start with. If at an point you need the query to run when the project table is empty then you need to change to a different table name with at least one row:

INSERT INTO projects (id ,projects, created) 
SELECT distinct '', 'PHP', '' FROM some_other_table
WHERE NOT EXISTS (SELECT *
                  FROM projects
                  WHERE projects = 'PHP')

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41827594
LIMIT 1 would probably work too and be more efficient than using "distinct"...
0
 
LVL 2

Author Comment

by:Amita Singh
ID: 41827599
Hi julian,
when i run query i get error

13.png
0
 
LVL 2

Author Comment

by:Amita Singh
ID: 41827602
my table structure
	CREATE TABLE `projects` (
  `id` int(11) NOT NULL,
  `projects` varchar(101) NOT NULL,
  `projects_info` varchar(255) NOT NULL,
  `created` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41827605
It would probably be sensible to give us a copy and paste of the structure of the projects table so we can see whether what you're asking is possible, or whether we need to change some values before it will work.

Did you try my solution?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41827606
This may be because of the other columns .. Every time when you insert it must be inserting some date in below column.

 `created` datetime NOT NULL


and because of that it is not considering the row as duplicate one. It must be a inserting todays date.
0
 
LVL 2

Author Comment

by:Amita Singh
ID: 41827609
Hi Terry,

when i get your query i get error.
14.png
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41827611
Can you please run the query:

SELECT VERSION();

Open in new window


so we know what version of MySQL you're running?
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41827613
Also, which software are you testing the query in? phpmyadmin?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41827615
Please check if you able to insert below data. Run one query at a time.


INSERT IGNORE INTO projects (id, projects,projects_info,created) VALUES ( '', 'PHP', '' , '2016/01/01')

INSERT IGNORE INTO projects (id, projects,projects_info,created) VALUES ( '', 'PHP', '' , '2016/01/01')
0
 
LVL 2

Author Comment

by:Amita Singh
ID: 41827620
VERSION is 10.1.13-MariaDB and i am using xampp phpmyadmin
0
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 41827625
Can you post screen grabs WITHOUT the error block blocking what your query looks like.

Something is not right - you are getting errors you shouldn't be getting - most of the solutions posted here are valid and should not generate errors - if you are getting errors then something else is not right.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41827629
That is weird... the query I provided was tested successfully in MySQL 10.0.27-MariaDB in PhpMyAdmin. I wonder if there's end of line characters doing something odd when it's pasted in?
0
 
LVL 2

Author Comment

by:Amita Singh
ID: 41827634
CREATE TABLE `projects` (
  `id` int(11) NOT NULL,
  `projects` varchar(101) NOT NULL,
  `projects_info` varchar(255) NOT NULL,
  `created` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `projects`
--
ALTER TABLE `projects`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `id` (`id`),
  ADD KEY `no_duplicate` (`projects`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `projects`
--
ALTER TABLE `projects`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41827636
Note though that you can't insert a blank value into the `id` column of the table unless you give the column AUTO_INCREMENT

Similarly, for the `created` column, it will need DEFAULT CURRENT_TIMESTAMP if you want to insert a blank value.

You may need to use NULL instead of '' for both columns
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41827639
I see `id` is ok then, but you'll still need to do something for `created`
0
 
LVL 2

Author Comment

by:Amita Singh
ID: 41827640
id is auto increment
15.png
0
 
LVL 57

Accepted Solution

by:
Julian Hansen earned 250 total points
ID: 41827650
ALTER TABLE `projects`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `id` (`id`),
  ADD KEY `no_duplicate` (`projects`);

Open in new window

On duplicate and ignore won't work unless projects is a unique key
Change to
ALTER TABLE `projects`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `id` (`id`),
  ADD UNIQUE KEY `no_duplicate` (`projects`);

Open in new window

0
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 125 total points
ID: 41827652
Oh... Id is the issue then in that case it will not able to compare. try below.

Run one by one...

INSERT IGNORE INTO projects (projects,projects_info,created) VALUES ('PHP', '' , '2016/01/01')

INSERT IGNORE INTO projects (projects,projects_info,created) VALUES ('PHP', '' , '2016/01/01')
0
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 41827653
I would also make createdate a timestamp field with a default value CURRENT_TIMESTAMP
alter table .`projects` 
   change `created` `created` timestamp default CURRENT_TIMESTAMP NOT NULL

Open in new window

0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 41827654
I said that there could be something related indexes...
As Terry is right suggesting you cannot insert simply a value per row without add also the required field values...
You're in good hands anyway with Terry and other participants experts
0
 
LVL 2

Author Comment

by:Amita Singh
ID: 41827745
Hi Julian,
when i run your  
ALTER TABLE `projects`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `id` (`id`),
  ADD UNIQUE KEY `no_duplicate` (`projects`);

Open in new window

this query error show
#1068 - Multiple primary key defined
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41827751
You still need to alter the table for the `created` column as per Julian's comment.
0
 
LVL 2

Author Comment

by:Amita Singh
ID: 41827754
i have alter the table for the `created` column its done but when i alter projects for UNIQUE KEY its show error.

#1062 - Duplicate entry 'AutoCAD' for key 'projects'
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41827755
Yes since you have duplicate entry in the table the engine is not allowing you to create the unique constraint.

Can you make it AutoCAD to AutoCAD1 for time being.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41827767
Do you want existing duplicate values to remain in the `projects` column? If so, then you don't want to be updating the existing data.

My suggested query will still work when there are existing duplicates, but the other suggestions that use INSERT IGNORE will not work in that case.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41827768
If  you are happy to remove or update records where the `projects` column is non-unique, then you'll be able to add a unique key on that column and INSERT IGNORE will work for what you're trying to do.
0
 
LVL 2

Author Comment

by:Amita Singh
ID: 41827776
Its work.

Thank you for giving your valuable time to me and solving my problem.

Thanx to all .
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41827787
So finally what you did. Unique projects column or Non unique.

Also could you please mark one or more answer as accepted solution and close the question.

Thanks !
0
 
LVL 2

Author Comment

by:Amita Singh
ID: 41827797
unique projects
0
 
LVL 2

Author Closing Comment

by:Amita Singh
ID: 41827810
Thanx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question