update if two fields exists, insert if not (MySQL)
insert, mysql
Solution
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).
Problem
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?