ON DUPLICATE KEY: unique multi-column constraint

Question

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 there is no other row with the pair (a, b) in the table (otherwise skip the insert).

Basically (a,b) shoud will be UNIQUE , not (a) or (b) , but both are related.

For example, these lines will 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 are 5,1 , 5,2 , 5.3 , 7.1 , etc.

But line number 2 should be considered here as a duplicate of line # 1, so line # 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 it possible?

Answer 1

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

 ALTER TABLE tblname ADD UNIQUE (a,b) 

Answer 2

 CREATE UNIQUE INDEX index_unique_on_a_and_b ON mytable (a,b) 

Answer 3

It is worth noting that if you try to add ADD UNIQUE (a, b) while there is data in the table, you are likely to get a duplicate key error. Clean the table, add a unique index, and it will work.