Mysql inserts and updates on DUPLICATE key update for batch update

On DUPLICATE key update batch update

Mysql has a batch update method, while mssql does not use this command. mysql can use this method for batch update. More powerful: If the parameter already exists, it is updated, if this parameter is not found in the database, a new one is inserted.

Example

Insert into xinhuazidian (a, B, c) VALUES ('AAA', 'BBB ', 'lao8. org '), ('aaa2', 'BBB','m .lao8.org ') on DUPLICATE key update B = VALUES (B), c = VALUES (c)

Judge Field a. Field a must be a unique index or unique primary key. If yes, update. If not, insert. update the B field and c field set after on DUPLICATE key update.

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 fields to be inserted after the select statement, even if these values do not exist in the current table.

Method 2:

 

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.

Only the reason why update cannot be inserted

Mysql insert into... on duplicate key... can be inserted only because:

The first field must be a unique index or unique primary key,

The first field must be a unique index or unique primary key,

The first field must be a unique index or unique primary key. Otherwise, only insert will be executed without update.