Insert into xxx on duplicate key update problem

For example, if the column a is defined as unique and has a value of 1, the following statement has the same effect, that is, if a=1 exists in the 1 denier entry record, c = c + 1 is updated directly without c = 3.

 
insert into table(a, b, c) values (1, 2, 3) on duplicate key 
update c = c + 1;1 update table set c = c + 1 where a = 1; 

It is also worth mentioning that this statement is known in mysql, but not in the standard sql statement.

INSERT INTO.. ON DUPLICATE KEY updates multi-line records

If ON DUPLICATE KEY UPDATE is specified at the end of the INSERT statement, and the insertion of a row results in a duplicate value in an UNIQUE index or PRIMARY KEY, then the old row UPDATE is executed. Insert a new row if it does not cause the problem of duplicate columns with only 1 value. For example, if the column a is defined as UNIQUE and contains a value of 1, the following two statements have the same effect:

 
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; If the original record is updated, the value of the affected row is 2.

If you want to know more about INSERT INTO.. For the function description of ON DUPLICATE KEY, see MySQL reference documentation: 13.2.4. INSERT syntax

Now the question is, if INSERT records multiple lines, what is the value of the field following ON DUPLICATE KEY UPDATE? To know that there can only be one ON DUPLICATE KEY UPDATE in an INSERT statement, will he update the record by 1 line or update all the lines that need to be updated? This problem has been bothering me for a long time. In fact, the problem of using VALUES() function 1 tangent has been solved.

For example, the field a is defined as UNIQUE, and records (2,2,9) and (3,2,1) already exist in the original database table table. If the a value of the inserted record is repeated with the original record, the original record will be updated; otherwise, a new row will be inserted:

 
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); 

When the above SQL statement is executed, only 1 value conflict occurs between a in (2,5,7) and the original record (2,5,9), ON DUPLICATE KEY UPDATE is executed, the original record (2,2,9) is updated to (2,5,9), and the original record (3,2,1) is updated to (3,3,1), and new records (1,2,3) and (4,8,2) are inserted.

Note: ON DUPLICATE KEY UPDATE is only MySQL's special syntax, not SQL's standard syntax!