INSERT INTO .. ON DUPLICATE KEY UPDATE with multiple keys

Question

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.

The purpose of the unique constraint is so that MySQL will reject any inserts which violate it. — Tim Biegeleisen, Jun 27 '16 at 10:01
In short.... I want to insert a row with columns "userid", "app" and "field" but if there is already a row with those 3 having the same value then it will update that row. If one of those is different meaning now other row maches, then it will insert it. — PixelPaul, Jun 27 '16 at 10:01
Hi Mahesh, none of those can be unique because the value may be used again for another row. — PixelPaul, Jun 27 '16 at 10:02
You can use insert into with on duplicate key update statment — Mahesh Madushanka, Jun 27 '16 at 10:02
Possible duplicate of [On Duplicate Key Update same as insert](http://stackoverflow.com/questions/14383503/on-duplicate-key-update-same-as-insert) — 1000111, Jun 27 '16 at 10:02
You need to SELECT first for checking if one of them are exists or not than you probably do insertion or updation — dod29, Jun 27 '16 at 10:04
You are missing the point, what @MaheshMadushanka is saying, is that you can create a unique index on multiple fields, not 3 indices on 3 separate fields.That matches your requirements exactly. — jeroen, Jun 27 '16 at 10:08

score 3 · Answer 1 · edited Jul 01 '16 at 00:16

3

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 = ?;

edited Jul 01 '16 at 00:16

Rick James

135,179
13
127
222

answered Jun 27 '16 at 10:07

Mahesh Madushanka

2,902
2
14
28

I created the new unique with 3 columns, i just tried this but it didn't create a new one, it updated one with a different userid INSERT INTO user_settings (field, app, userid, value) VALUES('approvaldefaultyes','apdible_servicebar',1,'1'),('emailcustomerdefaultyes','apdible_servicebar',1,'1') ON DUPLICATE KEY UPDATE value = VALUES(value) – PixelPaul Jun 27 '16 at 10:24
try it with above mention query in answer (replace question marks with your values) – Mahesh Madushanka Jun 27 '16 at 10:27
sorry pasted wrong one, this is the one but i am not sure it is working when i do multiple SET ( if that is possible? )...... INSERT INTO user_settings SET (field = 'approvaldefaultyes', app = 'apdible_servicebar', userid = 1, value = '1'),(field = 'emailcustomerdefaultyes', app = 'apdible_servicebar', userid = 1, value = '1') ON DUPLICATE KEY UPDATE value = valuedead – PixelPaul Jun 27 '16 at 10:34
soory i coudnt get your point what do you mean by multiple set – Mahesh Madushanka Jun 27 '16 at 10:36
well i am trying to insert or update 2 rows at the same time there. i guess i forgot to mention it above that i will always be inserting/update more then 1 row at once – PixelPaul Jun 27 '16 at 10:38
no you cant do it with above . if your doing it you have to do it one by one – Mahesh Madushanka Jun 27 '16 at 10:39
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/115710/discussion-between-mahesh-madushanka-and-pixelpaul). – Mahesh Madushanka Jun 27 '16 at 10:39

score 1 · Accepted Answer · edited Jul 01 '16 at 00:15

1

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

edited Jul 01 '16 at 00:15

Rick James

135,179
13
127
222

answered Jun 27 '16 at 11:20

PixelPaul

1,022
1
10
18

_And_ have that 3-column `UNIQUE` key. – Rick James Jul 01 '16 at 00:17

score 0 · Answer 3 · answered Jun 27 '16 at 10:03

0

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

answered Jun 27 '16 at 10:03

sorry, how does this insert the row if it doesn't already exist? – PixelPaul Jun 27 '16 at 10:08
this for update..to insert you should select : select * from mytable where (field = @v and app=@v1 and userid = @v2)..if there is a result returned then update using what i wrote in the answer..else insert normally as u r sure now that this is a new data – Jun 27 '16 at 10:10

INSERT INTO .. ON DUPLICATE KEY UPDATE with multiple keys

3 Answers3