Mysql replace and on duplicate key update statements

When sorting the viewing background, a requirement such as this is met, in the movie table there are ID (primary key auto-increment) and orderby (sort field ), suppose there are 10 Data IDs from 1 to 10, and the corresponding orderby is also from 1 to 10. Now I want to move the data with id = 9 to the third position (id = 3, and ensure the order of the previous data (that is, orderby = 4 for id = 3, orderby = 5 for id = 4... Orderby = 9) of id = 8, which can solve the data problem in a loop, but there are too many database operations in this way, now I want to use an SQL statement to solve this problem.

Next let's take a look at Mysql's REPLACE and INSERT... ON DUPLICATE KEY UPDATE

REPLACE

We may often encounter this situation when using databases. If a table creates a unique index on a field and inserts a record into the table using an existing key value, a primary key conflict error is thrown. Of course, we may want to overwrite the original record value with the new record value. If you use the traditional method, you must first use the DELETE statement to DELETE the original record, and then use INSERT to INSERT a new record. MySQL provides us with a new solution, which is the REPLACE statement. When you use REPLACE to INSERT a record, if there is no duplicate record, REPLACE is the same as the INSERT function. If there is a duplicate record, REPLACE replaces the original record value with the value of the new record.

The biggest advantage of using REPLACE is that you can combine DELETE and INSERT into one to form an atomic operation. In this way, you do not have to consider complicated operations such as adding transactions when using both DELETE and INSERT.

When using REPLACE, the table must have a unique index, and the field where the index is located cannot allow null values. Otherwise, REPLACE is exactly the same as INSERT.

After REPLACE is executed, the system returns the affected number of rows. If 1 is returned, no duplicate records exist in the table. If 2 is returned, a duplicate record exists, the system automatically calls DELETE to DELETE this record, and then inserts this record with INSERT. If the returned value is greater than 2, multiple unique indexes exist, and multiple records are deleted and inserted.

INSERT... ON DUPLICATE KEY UPDATE

INSERT... on duplicate key update: when inserting data, if the value of the primary KEY or unique index corresponding to the inserted data already exists in the table, modify the field value corresponding to the data. If it does not exist, insert it directly.

If you specify on duplicate key update and insert a row, DUPLICATE values will appear in a UNIQUE index or primary key, the old row will be updated. For example, if column a is defined as UNIQUE and contains a value of 1, the following two statements have the same effect:
Copy codeThe Code is as follows:
Mysql> insert into table (a, B, c) VALUES (1, 2, 3)

-> On duplicate key update c = c + 1;

Mysql> UPDATE table SET c = c + 1 WHERE a = 1;

If a 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.
NOTE: If Column B is also a unique column, INSERT is equivalent to the UPDATE statement.
Copy codeThe Code is as follows:
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. Generally, you should avoid using the on duplicate key clause for tables with multiple unique keywords.
You can use the VALUES (col_name) function in the UPDATE clause to reference the column VALUES from the INSERT part of the INSERT... UPDATE statement. In other words, if there is no duplicate keyword conflict, the VALUES (col_name) in the UPDATE clause can reference the value of the inserted col_name. This function is particularly applicable to multiline inserts. The VALUES () function only makes sense in the INSERT... UPDATE statement. Otherwise, NULL is returned.
When you use on duplicate key update, the DELAYED option is ignored.

The SQL statement for the problem above is:
Copy codeThe Code is as follows:
Insert into st_movie (id, orderby) values (9, 3), (3, 4), (4, 5), (5, 6), (6, 7), (7, 8), (8, 9) on duplicate key update 'orderby' = values ('orderby ');