Emma Emma - 1 month ago 11
MySQL Question

ON DUPLICATE KEY: multi-column unique constraint

Right now I have:

INSERT INTO mytable (a,b,c,d) VALUES(1,2,3,4)
ON DUPLICATE KEY UPDATE c=VALUES(c),d=VALUES(d)


which works if
a
or
b
are
UNIQUE
keys...

But now I want to UPDATE only when another row with the pair (a,b) doesn't exist in the table (otherwise skip insertion).

Basically
(a,b)
shoud be UNIQUE
, not
(a)
or
(b)
, but both connected.

For example these rows would be valid

ID (auto-inc) | a | b | c | d
0 | 5 | 1 | 343 |466
1 | 5 | 2 | 363 |466
2 | 5 | 3 | 343 |496
3 | 7 | 1 | 343 |496


Because there's
5,1
,
5,2
,
5.3
,
7.1
etc.

But row #2 here should be considered duplicate of row #1, so row #1 should be updated:

ID (auto-inc) | a | b | c | d
0 | 5 | 1 | 343 |466
1 | 5 | 1 | 363 |466
2 | 5 | 3 | 343 |496
3 | 7 | 1 | 343 |496


Is this possible?

Answer

make UNIQUE KEY to (a,b) not to b

ALTER TABLE tblname ADD UNIQUE (a,b)