MySQL - ignore insert error duplicate entry

Question

I am working in PHP   Please what s the proper way of inserting new records into the DB  which has unique field  I am inserting lot of records in a batch and I just want the new ones to be inserted and I don t want any error for the duplicate entry   Is there only way to first make a SELECT and to see if the entry is already there before the INSERT - and to INSERT only when SELECT returns no records  I hope not   I would like to somehow tell MySQL to ignore these inserts without any error   Thank you

User · Accepted Answer

You can use INSERT    IGNORE syntax if you want to take no action when there s a duplicate record   You can use REPLACE INTO syntax if you want to overwrite an old record with a new one with the same key   Or  you can use INSERT    ON DUPLICATE KEY UPDATE syntax if you want to perform an update to the record instead when you encounter a duplicate   Edit   Thought I d add some examples   Examples  Say you have a table named tbl with two columns  id and value   There is one entry  id 1 and value 1   If you run the following statements   REPLACE INTO tbl VALUES 1 50     You still have one record  with id 1 value 50   Note that the whole record was DELETED first however  and then re-inserted   Then   INSERT IGNORE INTO tbl VALUES  1 10     The operation executes successfully  but nothing is inserted   You still have id 1 and value 50   Finally   INSERT INTO tbl VALUES  1 200  ON DUPLICATE KEY UPDATE value 200    You now have a single record with id 1 and value 200

User · Answer

Try creating a duplicate table  preferably a temporary table  without the unique constraint and do your bulk load into that table  Then select only the unique  DISTINCT  items from the temporary table and insert into the target table

User · Answer

You can make sure that you do not insert duplicate information by using the EXISTS condition   For example  if you had a table named clients with a primary key of client id  you could use the following statement   INSERT INTO clients  client id  client name  client type  SELECT supplier id  supplier name   advertising  FROM suppliers WHERE not exists  select   from clients where clients client id   suppliers supplier id     This statement inserts multiple records with a subselect   If you wanted to insert a single record  you could use the following statement   INSERT INTO clients  client id  client name  client type  SELECT 10345   IBM    advertising  FROM dual WHERE not exists  select   from clients where clients client id   10345     The use of the dual table allows you to enter your values in a select statement  even though the values are not currently stored in a table   from http   www techonthenet com sql insert php

User · Answer

duplicate query mysql query  SELECT   FROM student   or die mysql error      duplicate mysql num rows  duplicate query   if  duplicate  0        while  value mysql fetch array  duplicate query                if   value  name     name  amp  amp    value  email     email  amp  amp    value  mobile     mobile  amp  amp    value  resume     resume                         echo  query  INSERT INTO student name email mobile resume VALUES   name    email    mobile    resume                  res mysql query  query               if  query                                echo  Success                             else                               echo  Error                             else                               echo  Duplicate Entry                                   else       echo  Records Already Exixts

User · Answer

You can use triggers   Also check this introduction guide to triggers

[php] MySQL - ignore insert error: duplicate entry

Examples

Examples related to php

Examples related to mysql

Examples related to insert