An example of using the INSERTINTO statement in MySql to update multiple data records is as follows:
The code is 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 ');
However, because the update syntax does not support updating multiple records at a time, you can only execute one record at a time:
The code is as follows:
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 (for example, 5000 items), this execution rate can be imagined.
This article introduces that the INSERT syntax in MySql has a condition duplicate key update. this syntax is applicable when you need to determine whether the record exists. if the record does not exist, INSERT the record that exists and then UPDATE the record.
For specific syntax, 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. However, when the primary key is duplicate, the field is updated. As follows:
The code is 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 a special MySQL syntax, not a standard SQL syntax!