Geek asks and answers

Mysql Paste on duplicated key with last_insert_id not working

create table test1 ( 
   id int not null auto_increment primary key, 
   a varchar(16), b varchar(16) 
);


INSERT INTO test1 (a,b) VALUES ('a1','b3') ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), b='3';

The above line should insert a record because the table is empty.

INSERT INTO test1 (a,b) VALUES ('a1','b3') ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), b='3';

Run the line again, it should replace bwith "3", since it a1, b3already exists. But mysql adds another line for me. I searched for a while and cannot find a solution.

Last update: Thanks for your help. I decided that one of the columns should be unique.

Modify table test1 add unique (a)

solve the problem of.

+3
source share
2 answers

ON DUPLICATEruns only if the column has UNIQUE KEY. Try adding UNIQUE INDEX to the table in the aor columns b, and it should work as intended.

+1

. primary key id

Try

INSERT INTO test1 (id,a,b) VALUES (1,'a1','b3') ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), b='3';

. .

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

.

+1

More articles:


All Articles