Mysql's replace and on DUPLICATE KEY UPDATE statement describes the problem solving instance _mysql

When you sort the backstage of the look, encountered a demand like this, in the movie table has ID (the primary key self-increase) and order by (sort fields), assuming that there are 10 data IDs from 1-10 respectively, corresponding to the order is also from 1-10, I now want to put id= 9 of the data is moved to the position of the third position (id=3), and the previous data order is guaranteed (that is, the orderby=9 of the orderby=5...id=8 of the id=3 orderby=4,id=4), so that if the problem of data can be solved in the form of a loop, But there are too many database procedures to do this, and now you want to use an SQL statement to solve the problem.

Here's a look at MySQL replace and insert ... On DUPLICATE KEY UPDATE

REPLACE

This may be the case when we use the database. If a table establishes a unique index on a field, when we insert a record into the table using the existing key value, it throws a primary key conflict error. Of course, we might want to overwrite the original record value with the value of the new record. If you use a traditional approach, you must first delete the original record by using the DELETE statement, and then insert the new record using insert. And in MySQL for us to provide a new solution, this is the replace statement. When you insert a record with replace, if you do not repeat, replace is the same as the Insert feature, and if there are duplicate records, replace replaces the original record value with the value of the new record.

The biggest advantage of using replace is that delete and insert can be combined to form an atomic operation. This eliminates the need to consider complex operations such as adding transactions while using both delete and insert.

When you use Replace, the table must have a unique index, and the field in which the index is located cannot allow null values, otherwise replace will be exactly the same as the insert.

After the replace is executed, the system returns the number of rows affected, and if you return 1, there is no duplicate record in the table, and if you return 2, there is a duplicate record, and the system automatically calls delete to delete the record, and then records the insertion with insert. If the returned value is greater than 2, then there are multiple unique indexes, 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 corresponding primary key or unique index in the inserted data already exists in the table, the value of the field corresponding to this piece of data is modified. If it does not exist, it is inserted directly.

If you specify on DUPLICATE key update and the insert row causes duplicate values to occur in a unique index or primary key, the old row update is executed. 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:

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 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.
NOTE: If column B is also a unique column, insert is equivalent to this UPDATE statement

Copy Code code as follows:

Mysql>update table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

If the a=1 OR b=2 matches more than one row, only one row is updated. In general, you should try to avoid using the on DUPLICATE key clause on tables with multiple unique keywords.
You can use the values (col_name) function in the UPDATE clause from the INSERT ... The insert portion of the UPDATE statement references the column value. In other words, if no duplicate keyword conflict occurs, values (col_name) in the update clause can reference the value of the inserted col_name. This function is especially useful for multiple rows of inserts. The VALUES () function is only in the insert ... is meaningful in the UPDATE statement and returns null at other times.
When you use on DUPLICATE KEY update, the delayed option is ignored.

The SQL statement for the problem above is:

Copy Code code 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 ' by ' VALUES (' by ');