In-depth analysis of MySQL "on DUPLICATE KEY UPDATE" syntax

Source: Internet
Author: User

MySQL "on DUPLICATE KEY UPDATE" syntax
if on DUPLICATE KEY UPDATE is specified at the end of the INSERT statement and the row is inserted causing duplicate values in a unique index or primary key, the update is performed on the row where the duplicate value occurs ; Insert a new row if the problem does not cause a unique value column to repeat.
For example, if column A is a primary key or has a unique index, and contains a value of 1, the following two statements have the same effect:

Copy Code code as follows:


INSERT into TABLE (a,c) VALUES (1,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 displayed as 1, and if the existing record is updated, the value of the affected row displays 2.
This syntax can also be used in this way:
If you insert a multiline record (assuming a is a primary key or a is a unique indexed column):

Copy Code code as follows:


INSERT into TABLE (a,c) VALUES (1,3), (1,7) on DUPLICATE KEY UPDATE c=c+1;


After execution, the value of C changes to 4 (the second one repeats with the first, and C is +1 on the original value).

Copy Code code as follows:


INSERT into TABLE (a,c) VALUES (1,3), (1,7) on DUPLICATE KEY UPDATE c=values (c);


After execution, the value of C changes to 7 (the second one repeats with the first, and C takes a duplicate value of 7 directly).
Note: On DUPLICATE KEY update is only a unique syntax for MySQL, not SQL standard syntax!
this syntax and suitability is used in situations where you need to determine whether a record exists, and if it does not exist, then the scene that is updated is inserted.

INSERT into.. On DUPLICATE key to update multiple-line records
if on DUPLICATE KEY UPDATE is specified at the end of the 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 if the problem does not cause the unique value column to repeat, 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:

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!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.