CODEWITHSUNDEEP
mysqlindexingupsert
PixelPaul
PixelPaul

Reputation: 1231

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.

Upvotes: 2

Views: 5076

Answers (3)

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2998

First create a unique index on (user_id, app, field).

ALTER TABLE user_settings
ADD UNIQUE (userid,app,field);

then use a query like this.

INSERT INTO user_settings SET
       -- id = ?,   Do not include this -- you don't have the value
       userid = ?,
        app=?,
        field=?,
        value = ?,
        ON DUPLICATE KEY UPDATE
        value = ?;

Upvotes: 3

PixelPaul
PixelPaul

Reputation: 1231

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

Upvotes: 1

user6349792
user6349792

Reputation:

You can do this

update mytable set col1 = @value where (field = @v and app=@v1)

if you want the three fields then:

 update mytable set col1 = @value where (field = @v and app=@v1 and userid = @v2)

you should make sure that the fields needed are joined with 'and' and all are enclosed in brackets

Upvotes: 0

Related Questions