INSERT into table (a,b,c) VALUES (All-in-all) on DUPLICATE KEY UPDATE c=c+1;
INSERT in the use of the DUPLICATE KEY update
If the on DUPLICATE KEY update is specified and the row is inserted causing duplicate values to occur in a unique index or primary KEY, the old line UPDATE is performed. For example, if column A is defined as unique and contains a value of 1, the following two statements have the same effect:
mysql> INSERT into table (a,b,c) VALUESOn DUPLICATE KEY UPDATE c=c+1;
mysql> 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 original record is updated, the value of the affected row is 2.NOTE: If column B is also the only column, the insert is equivalent to this UPDATE statement:mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
If A=1 OR b=2 matches multiple rows, only one row is updated. In general, you should try to avoid using the on DUPLICATE key clause on a table with multiple unique keywords.
You can use the values (col_name) function from the Insert ... in the UPDATE clause. The insert portion of the UPDATE statement refers to the column value. In other words, if a duplicate keyword conflict does not occur, values (col_name) in the update clause can refer to the value of the col_name being inserted. This function is especially useful for multi-row insertions. The VALUES () function is only in the insert ... The UPDATE statement makes sense, and returns null at other times.Example:mysql> INSERT into table (a,b,c) VALUES (4,5,6)On DUPLICATE KEY UPDATE c=values (a) +values (b);This statement functions the same as the following two statements: mysql> INSERT into table (a,b,c) VALUES, on DUPLICATE KEY UPDATE c=3; mysql> INSERT into table (a,b,c) VALUES (4,5,6), on DUPLICATE KEY UPDATE c=9;
INSERT ... SELECT
INSERT ... On DUPLICATE KEY UPDATE
insert ... On DUPLICATE REPLACE