The reason why mysql's on DUPLICATE key can only be inserted and cannot be updated

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.

Mysql inserts and updates on DUPLICATE key update for 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.

Insert into... on duplicate key update batch update

Insert into test_tbl (id, dr) values (1, '2'), (2, '3 '),... (x, 'y') on duplicate key update dr = values (dr );

Example

Insert into xinhuazidian (a, B, c) VALUES ('AAA', 'BBB ', '111cn. net '), ('aaa2', 'bbb2','m .111cn.net ') 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.

You can use this syntax to determine whether a record exists when inserting a record. If the record does not exist, insert the record. Otherwise, the record is updated, which is convenient and requires no execution of two SQL statements.

Insert into osc_visit_stats (stat_date, type, id, view_count) VALUES (?,?,?,?) On duplicate key update view_count = view_count +?
-- The osc_visit_stats table has a composite primary key (stat_date, type, id)

Multi-field update

Insert into osc_space_visit_records (space, user, visit_count, ip, visit_time) VALUES (?,?,?,?,?) On duplicate key update visit_count = visit_count + 1, ip = ?, Visit_time = NOW ()

I love mysql, a batch update method. The code that was originally executed in multiple lines is now fixed with one piece of code, and it is efficient.