mysql insert ignore duplicate key code example

Example 1: MySQL INSERT IGNORE Statement

When you use the INSERT statement to add multiple rows to a table and if an error occurs during the processing, MySQL terminates the statement and returns an error. As the result, no rows are inserted into the table.

However, if you use the INSERT IGNORE statement, the rows with invalid data that cause the error are ignored and the rows with valid data are inserted into the table.

The syntax of the INSERT IGNORE statement is as follows:

INSERT IGNORE INTO table(column_list)
VALUES( value_list),
      ( value_list),
      ...

Example 2: mysql on duplicate key update

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;
INSERT INTO t1 (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;

Example 3: mysql on duplicate key update

UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;