PixelPaul PixelPaul - 5 months ago 16
MySQL Question

INSERT INTO .. ON DUPLICATE KEY UPDATE with multiple keys

I have this table with the following columns:

id
userid
app
field
value


The columns "field" and "app" are unique together to the "userid". So the user will always only have 1 row that has the same "app" and "field" values together. But they will have more then one row that has just the "app" value the same or just the "field" value the same. And there will always be multiple rows with the same "userid".

I am not sure how i can do and insert or update on multiple keys. I can't set them as unique in mysql as the value can be used for other "userid" rows or other rows that has a different "app" or "field" value.

Is this possible or will i have to do a SELECT first to check if a row exists where the "userid", "app" and "field" match what i am wanting to insert or update.

Also, i am trying to insert/update more then 1 row with a single query.

Answer

This is what worked for me...

INSERT INTO table (field, app, userid, value)
           VALUES (1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4)
           ON DUPLICATE KEY UPDATE value=VALUES(value);

this works doing multiple rows with a single query