ON DUPLICATE KEY: multi-column unique constraint

Question

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 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)