2

This isn't an (exact) duplicate of this questions so I'Ve started a new one.

I have this table (ID is primary and auto increment)

ID | mykey | myfoo | mybar
============================
 1 | 1.1   | abc   | 123
 2 | 1.1.1 | def   | 456
 3 | 1.2   | abc   | 789
 4 | 1.1   | ghi   | 999

I would like to UPDATE row 1 with mybar = "333" only if mykey = '1.1' AND myfoo = 'abc'

If either mykey != '1.1' OR myfoo != 'abc' I would like to INSERT an new row.

Is this possible with one statement?

Community
  • 1
  • 1
Xaver
  • 11,144
  • 13
  • 56
  • 91

1 Answers1

8

A unique index in MySQL does not have to be on a single column. You can add a UNIQUE index on multiple columns simply by specifying more columns in your ALTER TABLE..ADD UNIQUE statement:

ALTER TABLE myTable ADD UNIQUE (
    mykey,
    myfoo
);

Now you can use a regular INSERT INTO...ON DUPLICATE KEY statement.

SQLFiddle DEMO (note that the multiple repeated values are not added - all others are)

Note:

If either is NULL, it will not be counted as unique. mykey being 'bar' and myfoo being NULL could be added to infinity even though they have the "same" values (NULL isn't really a value).

h2ooooooo
  • 39,111
  • 8
  • 68
  • 102
  • Thanks! never thought this is possible :) – Xaver Nov 26 '13 at 17:57
  • 2
    The SQLFiddle demo seems to be demonstrating that duplicate values are ignored. The answer is correct but the demo would be better if it showed `INSERT INTO...ON DUPLICATE KEY` in action. – John Mar 11 '16 at 14:26