• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

duplicate data not insert in mysql

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
Amita Singh
Asked:
Amita Singh
  • 16
  • 12
  • 10
  • +2
3 Solutions
 
Pawan KumarDatabase ExpertCommented:
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
 
Terry WoodsIT GuruCommented:
Are you just wanting to insert one row when it doesn't exist?
0
 
Amita SinghWeb DeveloperAuthor Commented:
yes. if data exit its skip insert or data not exit its insert.
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Pawan KumarDatabase ExpertCommented:
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
 
Amita SinghWeb DeveloperAuthor Commented:
sql show error when i run query

11.png
0
 
Pawan KumarDatabase ExpertCommented:
Please try the last one..
0
 
Amita SinghWeb DeveloperAuthor Commented:
same error..

12.png
0
 
Pawan KumarDatabase ExpertCommented:
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
 
Amita SinghWeb DeveloperAuthor Commented:
i m facing same problem.
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
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
 
Amita SinghWeb DeveloperAuthor Commented:
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
 
Julian HansenCommented:
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
 
Pawan KumarDatabase ExpertCommented:
Try this..

INSERT IGNORE INTO projects (id, projects,created) VALUES ( '', 'PHP', '' )
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
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
 
Julian HansenCommented:
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
 
Terry WoodsIT GuruCommented:
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
 
Terry WoodsIT GuruCommented:
LIMIT 1 would probably work too and be more efficient than using "distinct"...
0
 
Amita SinghWeb DeveloperAuthor Commented:
Hi julian,
when i run query i get error

13.png
0
 
Amita SinghWeb DeveloperAuthor Commented:
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
 
Terry WoodsIT GuruCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Amita SinghWeb DeveloperAuthor Commented:
Hi Terry,

when i get your query i get error.
14.png
0
 
Terry WoodsIT GuruCommented:
Can you please run the query:

SELECT VERSION();

Open in new window


so we know what version of MySQL you're running?
0
 
Terry WoodsIT GuruCommented:
Also, which software are you testing the query in? phpmyadmin?
0
 
Pawan KumarDatabase ExpertCommented:
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
 
Amita SinghWeb DeveloperAuthor Commented:
VERSION is 10.1.13-MariaDB and i am using xampp phpmyadmin
0
 
Julian HansenCommented:
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
 
Terry WoodsIT GuruCommented:
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
 
Amita SinghWeb DeveloperAuthor Commented:
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
 
Terry WoodsIT GuruCommented:
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
 
Terry WoodsIT GuruCommented:
I see `id` is ok then, but you'll still need to do something for `created`
0
 
Amita SinghWeb DeveloperAuthor Commented:
id is auto increment
15.png
0
 
Julian HansenCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Julian HansenCommented:
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
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
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
 
Amita SinghWeb DeveloperAuthor Commented:
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
 
Terry WoodsIT GuruCommented:
You still need to alter the table for the `created` column as per Julian's comment.
0
 
Amita SinghWeb DeveloperAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Terry WoodsIT GuruCommented:
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
 
Terry WoodsIT GuruCommented:
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
 
Amita SinghWeb DeveloperAuthor Commented:
Its work.

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

Thanx to all .
0
 
Pawan KumarDatabase ExpertCommented:
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
 
Amita SinghWeb DeveloperAuthor Commented:
unique projects
0
 
Amita SinghWeb DeveloperAuthor Commented:
Thanx
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 16
  • 12
  • 10
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now