MySQL insert when the record does not exist and update when the record exists

When the MySQL record does not exist, insert. When the record exists, there are basically three solutions to update the internet. First: Example 1: insert multiple records. Suppose There Is A client_id table with the primary key, you can use the following statement: INSERTINTOclients (client_id, client_name, client_type) SELECTsupplier _

When the MySQL record does not exist, insert. When the record exists, there are basically three solutions to update the internet. First: Example 1: insert multiple records. Suppose There Is A client_id table with the primary key, you can use the following statement: insert into clients (client_id, client_name, client_type) SELECT supplier _

MySQL insert when the record does not exist and update when the record exists

There are basically three solutions on the Internet.

First:

Example 1: insert multiple records

Suppose there is a clients table with the primary key of client_id, you can use the following statement:

Insert into clients

(Client_id, client_name, client_type)

SELECT supplier_id, supplier_name, 'advertising'

FROM suppliers

WHERE not exists (select * from clients

Where clients. client_id = suppliers. supplier_id );

Example 1: insert a single record

Insert into clients

(Client_id, client_name, client_type)

SELECT 10345, 'ibm ', 'advertising'

FROM dual

WHERE not exists (select * from clients

Where clients. client_id = 10345 );

Using dual as the table name allows you to directly keep up with the values of the insert field after the select statement, even if these values do not exist in the current table.

Second:

Use of on duplicate key update in INSERT

If you specify on duplicate key update and insert a row causes DUPLICATE values in a UNIQUE index or primary key, the old row is 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, 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 no duplicate keyword conflict occurs, VALUES (col_name) in the UPDATE clause can reference the value of col_name to be inserted. This function is particularly applicable to multiple insert statements. 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.

Third:

REPLACE statement

We may often encounter this situation when using databases. If a table creates a unique index on a field, when we insert a record into the table with an existing key value, a primary key conflict error will be 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 INSERTinsert to create a new record. MySQL provides us with a new solution, which is the REPLACE statement. When you use REPLACEinsert to INSERT a record, if there are no duplicates, 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 the record first, and then inserts the record with INSERT. If the returned value is greater than 2, there are multiple unique indexes, and multiple records are deleted and inserted.

The REPLACE syntax is very similar to INSERT. The following REPLACE statement is insert or update 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 then 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 into 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

As we can see, REPLACE deletes all the three original records, and then inserts (1, 2, 3.

Summary: Although there is no specific test, I feel that the first kind of resource is the most expensive (just feeling), but if you do not have a primary key, you can only use it. The difference between the second and third types is: 1) insert is to try insert first, and update if the primary key exists. REPLACE is to first try insert. If the primary key exists, the original record is deleted and then insert. 2) if multiple unique keywords conflict (conflicts with different keywords occur in different records ), for example, if there are two fields and two records in conflict (no records conflict with one field), insert selects the sorting rule and updates the previous one. REPLACE deletes the two records, then insert a new record. If you have any mistakes, please correct me.

Source Document