Mysql:on DUPLICATE KEY UPDATE usage

You can use this syntax to determine whether a record is present when you insert it, insert it if it does not exist, or update it so that it is convenient to perform no two SQL

This statement is knowledge of MySQL, which is not in the standard SQL statement.

INSERT into.. On DUPLICATE key updates multiple rows of records

if an on DUPLICATE KEY UPDATE is specified at the end of the INSERT statement, and the row is inserted resulting in a duplicate value in a unique index or primary KEY, the old row UPDATE is performed, or if the unique value column is not duplicated. The new row is inserted. For example, if column A is defined as unique and contains a value of 1, the following two statements have the same effect:
the original 3 SQL statements were executed as follows:
The code is as follows:

IF (SELECT * from t_table WHERE id=1001 ') {UPDATE t_table SET cnt=cnt+1 WHERE id=1001;} else {INSERT into IP Stats (ID, CNT) VALUES (1001, 1);}

Now, the code looks like this:

INSERT INTO t_table (id,cnt) VALUES (1001,2) on duplicate key update cnt=cnt+1;
Update t_table set cnt=cnt+1 WHERE id=1001;

if the row is inserted as a new record, the value of the affected row is 1, and if the original record is updated, the value of the affected row is 2.

Now the question is, if the insert multiline record, on DUPLICATE KEY update after the field value how to specify? Be aware that there can be only one on DUPLICATE KEY update in an INSERT statement, whether he will update a row of records or update all rows that need to be updated. This problem has plagued me for a long time, in fact, the use of the values () function all solve the problem.

For example, field A is defined as unique, and records (2,2,9) and (3,2,1) exist in the original database table tables, and if the a value of the inserted record is duplicated with the original record, the original record is updated or the new row is inserted:

The code is as follows:

INSERT into TABLE (a,b,c) VALUES (All-in-all), (2,5,7), (3,3,6), (4,8,2) on DUPLICATE KEY UPDATE b=values (b);

in the execution of the above SQL statement, Discovery (2,5,7) has a unique value conflict with the original record (2,2,9), then the on DUPLICATE KEY update is performed, the original record (2,2,9) is updated to (2,5,9) and (3,2,1) is updated ( 3,3,1), inserting new records (--) and (4,8,2)

Note: on DUPLICATE KEY update is only a unique syntax for MySQL, not SQL standard syntax!