If the SQL statement inserts duplicate primary keys or data already exists, update the data.

This is often the case when developing databases:

When a piece of data does not exist, insert the data. If the primary key of the data already exists in the database, update the data.

How do you do this? First, query data based on the primary key, and then determine whether there is data. If there is data, update the field; otherwise, insert the data.

The disadvantage of this is that you need to connect to the database server twice and then use advanced languages to determine whether the logic exists.

Next we will teach you an SQL statement to solve these problems!

For example, the table structure of the weixin_user data table is as follows: (blog transfer, picture loss)

$ SQL = "insert into weixin_user (wx_id, wx_name, wx_state, wx_info, wx_lasttime) values ('$ wx_id', '$ wx_name', '$ wx_state', '$ wx_info ', NOW () on duplicate key update wx_name = '$ wx_name', wx_state = '$ wx_state', wx_info = '$ wx_info', wx_lasttime = NOW ();";

The preceding SQL statement is an SQL statement (weixin_user is used to record the status of WeChat fans) in the WeChat and email interface development code of this blog. The following figure shows the code, you can see this, right?

On duplicate key update (when the duplicate key primary key repeat error occurs, trigger the Update operation. Of course, the requirement is that the table must have the primary KEY during design)

What is the specific effect? Let's just look at the example.

For example, if Column a is a primary key or has a UNIQUE index with a value of 1, the following two statements have the same effect:

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 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.
This syntax can also be used as follows:

If you INSERT multiple rows of records (assuming a is the primary key or a is a UNIQUE index column ):

Insert into table (a, c) VALUES (1, 3), (1, 7) on duplicate key update c = c + 1;

After Execution, the value of c is changed to 4 (the second repeat with the first one, and c is on the original value + 1 ).

Insert into table (a, c) VALUES (1, 3), (1, 7) on duplicate key update c = VALUES (c );

After the execution, the value of c will change to 7 (the second repeat with the first repeat, and c will take the repeat value 7 directly ).
Note: on duplicate key update is only a special MySQL syntax, not a standard SQL syntax!
This syntax is applicable to scenarios where you need to determine whether a record exists, and if no record exists, insert the statement and update the statement.

Insert into .. on duplicate key to update multiple rows
If on duplicate key update is specified at the end of the INSERT statement, and DUPLICATE values appear in a UNIQUE index or primary key after the row is inserted, the old row UPDATE is executed; if the unique value column is not duplicate, a 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:

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 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.
For more information about the insert into... on duplicate key function, see MySQL Reference Documentation: 13.2.4. INSERT syntax.

Now the question is, how can I specify the value of the field after ON DUPLICATE KEY UPDATE if multiple rows are inserted? You must know that only one on duplicate key update exists in an INSERT statement. It will UPDATE a row of records or all rows to be updated. This problem has plagued me for a long time. In fact, all problems are solved by using the VALUES () function.

For example, field a is defined as UNIQUE, and records (, 9) and (, 1) exist in the table of the original database ), if the value of the inserted record is the same as the original record, the original record is updated; otherwise, a new row is inserted:

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 );

When the preceding SQL statement is executed, if a in (, 7) conflicts with the unique value of the original record (, 9), the ON DUPLICATE KEY UPDATE statement is executed to UPDATE the original record, 9) update (, 9), update (, 1) to (, 3, 1), insert new Records (, 3), and (, 2)
Note: on duplicate key update is only a special MySQL syntax, not a standard SQL syntax!

Use of on duplicate key update in INSERT (this article focuses ON)
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:
 

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 this UPDATE statement:
 

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 section 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.
Example:

Mysql> insert into table (a, B, c) VALUES (1, 2, 3), (4, 5, 6)
-> On duplicate key update c = VALUES (a) + VALUES (B );
 
This statement serves the same purpose as the following two statements:
 
Mysql> insert into table (a, B, c) VALUES (1, 2, 3)
-> On duplicate key update c = 3;
Mysql> insert into table (a, B, c) VALUES (4, 5, 6)
-> On duplicate key update c = 9;
 
When you use on duplicate key update, the DELAYED option is ignored.
Method 3:
 
REPLACE statement
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.

The REPLACE syntax is very similar to INSERT. The following REPLACE statement inserts or updates a record.

Replace into users (id, name, age) VALUES (123, 'Zhao Benshan ', 50 );
 

Insert multiple records:

Replace into users (id, name, age)

VALUES (123, 'Zhao Benshan ', 50), (134, 'Mary', 15 );
 
REPLACE can also use the SET statement.
 
Replace into users SET id = 123, name = 'Zhao Benshan ', age = 50;
 

As mentioned above, REPLACE may affect more than three records because there is more than one unique index in the table. In this case, REPLACE considers each unique index, deletes the duplicate record corresponding to each index, and inserts this new record. Assume that there is a table 1 with three fields a, B, and c. They all have a unique index.

Create table table1 (a int not null unique, B INT NOT NULL UNIQUE, c INT NOT NULL UNIQUE );
 

Assume that three records exist in Table 1.

A B c
1 1 1
2 2 2
3 3 3

Next, we use the REPLACE statement to insert a record to Table 1.

Replace into table1 (a, B, c) VALUES (1, 2, 3 );
 

The returned result is as follows:

Query OK, 4 rows affected (0.00 sec)

The record in Table 1 is as follows:

A B c
1 2 3