INSERT ... ON DUPLICATE KEY UPDATE (Two keys to compare)

Question

What I want to do is to just increment the 4th column of the value that it should've been if it didn't existed, when a row that has col0 = valuecol0 AND col2 = valuecol2 is in the table. Otherwise, I want to insert the row, of course.

INSERT INTO tablename (col0, col1, col2, col3) 
VALUES (valuecol0, valuecol1, valuecol2, valuecol3) 
ON DUPLICATE KEY UPDATE col3 = col3 + VALUES( col3 );

Anyway, I can't figure out how I should set the INDEXes in the table, I read the page in the documentation here but it wans't much of help. So I tried all the four possible combination and no one worked!

Where am I failing?

Answer 1

To generate a key violation, you can use a unique index:

create unique index IX_YourTable_Col0Col2 on YourTable (col0, col2);

With this index, you can use the on duplicate key update syntax:

insert into YourTable (col0, col1, col2, col3) 
values (1, 2, 1, 2) 
on duplicate key update col3 = col3 + 2;

Example at SQL Fiddle.