2007-07-09

MySQL : INSERT ... ON DUPLICATE KEY UPDATE

Whats happen with new record that conflicts with an existing one?
It seems like it's missed in space:
-----------------------------------------------------
create database if not exists testdups;
use testdups;
drop table if exists test1, test2;

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

create table test2 (
id int not null auto_increment primary_key,
a varchar(16),
b varchar(16)
);

insert table1(a, b) values
('a1', 'b1'),
('a1', 'b2'),
('a1', 'b2'),
('a1', 'b3')
);

alter table test2 add unique ab(a, b);

insert into test2 select * from test1 on duplicate key update a = 'REMOVE-ME';

mysql> select * from test2;

+----+-----------+------+
| id | a | b |
+----+-----------+------+
| 1 | a1 | b1 |
| 2 | REMOVE-ME | b2 |
| 4 | a1 | b3 |
+----+-----------+------+

No comments: