Detailed explanation of MYSQL's update syntax for non-repeated insertion of data

The update operation is one of the most common operations to use a database. The following describes the update Syntax of data that is not inserted repeatedly in MYSQL for your reference. It is helpful for you to learn how to update data.

If you specify the on duplicate key update command statement, the unique index or primary index will not insert content that is duplicated with the database record, but will also UPDATE the old records in the database. For example, if field a is declared as a unique index and contains only records with a value of 1, the following two statements will achieve the same effect:

1. insert into table (a, B, c) VALUES (1, 2, 3)
On duplicate key update c = c + 1;

2. UPDATE table SET c = c + 1 WHERE a = 1;

The affected rows are rows a = 1. When inserting, the value of c is added to 1.
If field B is unique, the insert statement will be the same as the following statement:
UPDATE table SET c = c + 1 WHERE a = 1 OR B = 2 LIMIT 1;
If a = 1 OR B = 2 matches more than one row, the first row is updated. Generally, if a table has multiple unique indexes, you should avoid using the on duplicate key clause.
You can use the VALUES (field name) function in insert update statement INSERT... UPDATE to associate a row of records. That is to say, VALUES (field name) can be used in the UPDATE statement to UPDATE the value of a field without duplicate keys. This function is particularly useful in multiline inserts. However, the VALUES () function is only valid in the INSERT... UPDATE statement. Otherwise, NULL is returned. For example:

Insert into table (a, B, c) VALUES (1, 2, 3), (4, 5, 6)
On duplicate key update c = VALUES (a) + VALUES (B );
This statement has the same effect as the following two statements:

Insert into table (a, B, c) VALUES (1, 2, 3)
On duplicate key update c = 3;
Insert into table (a, B, c) VALUES (4, 5, 6)
On duplicate key update c = 9;
If the table contains an auto increment field AUTO_INCREMENT, and INSERT... UPDATE inserts a row. The function LAST_INSERT_ID () returns the value of AUTO_INCREMENT. If this statement updates a row, LAST_INSERT_ID () is meaningless. However, you can use LAST_INSERT_ID (expr) to make it meaningful. If the id field is auto-incrementing, LAST_INSERT_ID () can be meaningful to the update statement as follows:

Insert into table (a, B, c) VALUES (1, 2, 3)
On duplicate key update id = LAST_INSERT_ID (id), c = 3;
If you use the on duplicate key update statement, the DELAYED execution option DELAYED will be ignored.

 

How to update data in batches in two associated tables in SQL

Introduction to data update monitoring CDC

Obtain the system date by using SQL Functions

How to traverse a BOM using SQL Functions

SQL function used to judge a leap year