Mysql Insert Or Update Syntax example

Sometimes you will need to write an insert SQL, or update if the primary key exists, or insert if the primary key does not exist. This usage is provided in MySQL: ON DUPLICATE KEY UPDATE . Let's see how it's used!

First the original data for the database is as follows:

a   b   c1   b1  c12   b2  c23   b3  c3

This will cause an error if you execute the following SQL

INSERT INTO test VALUES(1,‘b4‘,‘c4‘);

Error message as follows, prompt cannot be inserted repeatedly:

1 queries executed, 0 success, 1 errors, 0 warnings查询：insert into test values(1,‘b4‘,‘c4‘)错误代码： 1062Duplicate entry ‘1‘ for key ‘PRIMARY‘执行耗时 : 0 sec传送时间 : 0 sec总耗时 : 0.008 sec

At this point, you can use ON DUPLICATE KEY UPDATE it, which means that the previous insert is executed first, and if the primary key is repeated, the subsequent update

INSERT INTO test VALUES (1,‘b4‘,‘c4‘) ON DUPLICATE KEY UPDATE b=VALUES(b),c=VALUES(c);

The above SQL can be simply understood as:

select count(1) from test where a=1;if count(1) > 0UPDATE test SET b=‘xxx‘,c=‘xxx‘ WHERE a=1;

After execution, you can see that there are two lines of the impact (as to why two lines are affected, you have to study the underlying implementation, you can refer to the official documentation):

1 queries executed, 1 success, 0 errors, 0 warnings查询：INSERT INTO test VALUES (1,‘b4‘,‘c4‘) ON DUPLICATE KEY UPDATE b=VALUES(b),c=VALUES(c)共 2 行受到影响执行耗时 : 0.023 sec传送时间 : 0 sec总耗时 : 0.023 sec

After execution, the data becomes the following:

a   b   c1   b4  c42   b2  c23   b3  c3

Mysql Insert Or Update Syntax example