Solved

duplicate data not insert in mysql

Posted on 2016-10-04
46
46 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
  • 16
  • 12
  • 10
  • +2
46 Comments
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

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

Author Comment

by:Amita Singh
ID: 41827570
same error..

12.png
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
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 51

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 18

Expert Comment

by:Pawan Kumar Khowal
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 51

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 18

Expert Comment

by:Pawan Kumar Khowal
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 35

Expert Comment

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

Expert Comment

by:Pawan Kumar Khowal
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 51

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 51

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 18

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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 51

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 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

by:Pawan Kumar Khowal
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
This article discusses how to create an extensible mechanism for linked drop downs.
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 look for a specific file type in a local or remote server directory using PHP.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now