MySQL – ignore insert error: duplicate entry

by

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.

More Related Contents:

  1. Using PHP read word in textarea line by line and insert to mySQL
  2. How to insert multiple rows from array using CodeIgniter framework?
  3. PHP mysql insert date format
  4. Incorrect Integer (2147483647) is inserted into MySQL?
  5. Escaping single quote in PHP when inserting into MySQL [duplicate]
  6. PHP/MySQL Insert null values
  7. Object of class DateTime could not be converted to string
  8. php run once and insert twice in mysql database
  9. PHP mySQL – Insert new record into table with auto-increment on primary key
  10. Replacing \r\n with PHP
  11. What to put in database columns when creating registration/login system? (xampp)
  12. mysql_fetch_array()/mysql_fetch_assoc()/mysql_fetch_row()/mysql_num_rows etc… expects parameter 1 to be resource
  13. How to properly set up a PDO connection
  14. Search with comma-separated value mysql
  15. PDO Prepared Inserts multiple rows in single query
  16. How to do this in Laravel, subquery where in
  17. PHP MySQLI Prevent SQL Injection [duplicate]
  18. mysql PDO how to bind LIKE
  19. mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in [duplicate]
  20. How to write a REST API?
  21. how to access the command line for xampp on windows
  22. How can I solve incompatible with sql_mode=only_full_group_by in laravel eloquent?
  23. PHP, MySQL and Time Zones
  24. How do I loop through a MySQL query via PDO in PHP?
  25. Getting time difference between two times in PHP [duplicate]
  26. Using LIKE in bindParam for a MySQL PDO Query [duplicate]
  27. Formatting an SQL timestamp with PHP
  28. Finding free blocks of time in mysql and php?
  29. How to check if a value already exists to avoid duplicates?
  30. How to use MySQL Found_Rows() in PHP?

Leave a Comment