INSERT into.. On DUPLICATE key updates multiple-line records _mysql

If on DUPLICATE KEY UPDATE is specified at the end of an INSERT statement, and the insert row causes duplicate values to occur in a unique index or primary key, the old row update is executed, and the new row is inserted if the problem does not cause the unique value column to repeat. For example, if column A is defined as unique and contains a value of 1, the following two statements have the same effect:

Copy Code code as follows:

INSERT into TABLE (a,b,c)
VALUES (1,2,3) on DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;

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

If you want to learn more about insert INTO. On DUPLICATE Key's function description, see MySQL Reference document: 13.2.4. Insert Syntax

Now the question is, how do you specify the value of the field after DUPLICATE KEY Update if you insert a multiline record? To know that there can be only one on DUPLICATE KEY update in an INSERT statement, whether he updates a row of records or updates all the rows that need to be updated. This problem has plagued me for a long time, in fact, using the values () function all the problems are solved.

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

Copy Code code as follows:

INSERT into TABLE (a,b,c) VALUES
(1,2,3),
(2,5,7),
(3,3,6),
(4,8,2)
On DUPLICATE KEY UPDATE b=values (b);

Execution of the above SQL statement discovers a unique value conflict with the original record (2,2,9) in (2,5,7), executes on DUPLICATE KEY update, updates the original record (2,2,9) to (2,5,9), Updates (3,2,1) to (3,3,1), Insert new Record (1,2,3) and (4,8,2)

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