![]() MySQL INSERT ON DUPLICATE KEY UPDATE example The statement above sets the value of the c1 to its current value specified by the expression VALUES(c1) plus 1 if there is a duplicate in UNIQUE index or PRIMARY KEY. ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + 1 To use the values from the INSERT clause in the DUPLICATE KEY UPDATE clause, you use the VALUES() function as follows: INSERT INTO table_name(c1) ![]() If the existing row is updated using its current values, the number of affected rows is 0.If the existing row is updated, the number of affected rows is 2.If the new row is inserted, the number of affected rows is 1.MySQL returns the number of affected rows based on the action it performs: If a duplicate error occurs, it will update the existing row with the value specified in the ON DUPLICATE KEY UPDATE clause. The only addition to the INSERT statement is the ON DUPLICATE KEY UPDATE clause where you specify a list of column-value-pair assignments in case of duplicate.īasically, the statement first tries to insert a new row into the table. Code language: SQL (Structured Query Language) ( sql ) The syntax of INSERT ON DUPLICATE KEY UPDATE statement is as follows: INSERT INTO table (column_list) However, if you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement, MySQL will update the existing row with the new values instead. ![]() When you insert a new row into a table if the row causes a duplicate in UNIQUE index or PRIMARY KEY, MySQL will issue an error. The INSERT ON DUPLICATE KEY UPDATE is a MySQL’s extension to the SQL standard’s INSERT statement. Introduction to the MySQL INSERT ON DUPLICATE KEY UPDATE statement Summary: in this tutorial, you will learn how to use MySQL INSERT ON DUPLICATE KEY UPDATE statement to update data if a duplicate in the UNIQUE index or PRIMARY KEY error occurs when you insert a row into a table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |