MySql example of using INSERT INTO statements to update multiple pieces of data

  • 2020-06-15 10:24:31
  • OfStack

We know that insert supports multiple statements when inserting multiple data:


INSERT INTO t_member (id, name, email) VALUES
    (1, 'nick', 'nick@126.com'),
    (4, 'angel','angel@163.com'),
    (7, 'brank','ba198@126.com');

However, for updating records, since the update syntax does not support updating multiple records at a time, only one record can be executed:


UPDATE t_member SET name='nick', email='nick@126.com' WHERE id=1;
UPDATE t_member SET name='angel', email='angel@163.com' WHERE id=4;
UPDATE t_member SET name='brank', email='ba198@126.com' WHERE id=7;

Here the problem arises. If the update list is very large (say 5000 pieces), the implementation rate can be imagined.

This will introduce 1 in MySql INSERT grammar has a condition DUPLICATE KEY UPDATE, this grammar and suitable for the need to determine whether the record exists, does not exist to insert the existence of the updated record.

Specific syntax can see: http: / / dev mysql. com doc/refman / 5.0 / en/insert html

Based on the above situation, the insert statement is still used for updating records, but the fields are updated when primary key duplication is restricted. As follows:


INSERT INTO t_member (id, name, email) VALUES
    (1, 'nick', 'nick@126.com'),
    (4, 'angel','angel@163.com'),
    (7, 'brank','ba198@126.com')
ON DUPLICATE KEY UPDATE name=VALUES(name), email=VALUES(email);

Note: ON DUPLICATE KEY UPDATE is only MySQL specific syntax, not SQL standard syntax!


Related articles: