SQL `update` if combination of keys exists in row - else create new row

Question

First of, I've searched this topic here and elsewhere online, and found numorous articles and answers, but none of which did this...

I have a table with ratings, and you should be able to update your rating, but not create a new row.

My table contains: productId, rating, userId

If a row with productId and userId exists, then update rating. Else create new row.

How do I do this?

Yes, but it's not a duplicate key. The userId could rate other productIds and other users could too rate... — curly_brackets, Jun 06 '13 at 19:03
Two columns can together act as primary key, so then it will be a duplicate key — Dirk McQuickly, Jun 06 '13 at 19:06
With a unique (or primary key) constraint on `(userID, productID)`, the `ON DUPLICATE KEY UPDATE` construct would work fine. — ypercubeᵀᴹ, Jun 06 '13 at 19:07
possible duplicate of [If two same fields exists insert else update](http://stackoverflow.com/questions/9136695/if-two-same-fields-exists-insert-else-update) — ypercubeᵀᴹ, Jun 06 '13 at 19:18

ypercubeᵀᴹ · Accepted Answer · 2013-06-06T21:53:23.243

4

First add a UNIQUE constraint:

ALTER TABLE tableX
  ADD CONSTRAINT productId_userId_UQ
    UNIQUE (productId, userId) ;

Then you can use the INSERT ... ON DUPLICATE KEY UPDATE construction:

INSERT INTO tableX
  (productId, userId, rating)
VALUES
  (101, 42, 5),
  (102, 42, 6),
  (103, 42, 0)
ON DUPLICATE KEY UPDATE
  rating = VALUES(rating) ;

See the SQL-Fiddle

edited Jun 06 '13 at 21:53

answered Jun 06 '13 at 19:22

ypercubeᵀᴹ

113,259
19
174
235

This does not allow me to rate more than one product... It only updates the rating on the first product... – curly_brackets Jun 06 '13 at 21:46

score 0 · Answer 2 · answered Jun 06 '13 at 19:02

0

You are missing something or need to provide more information. Your program has to perform a SQL query (a SELECT statement) to find out if the table contains a row with a given productId and userId, then perform a UPDATE statement to update the rating, otherwise perform a INSERT to insert the new row. These are separate steps unless you group them into a stored procedure.

answered Jun 06 '13 at 19:02

Piovezan

3,215
1
28
45

How do I make these seperate steps with one call from PHP? Can you guide me to an example or show me? – curly_brackets Jun 06 '13 at 19:06

score 0 · Answer 3 · answered Jun 06 '13 at 19:03

0

Use a REPLACE INTO query.

REPLACE INTO table (productId, userId, rating) VALUES ('product id', 'user id', 'rating');

REPLACE INTO is like a normal insert, except if it finds a row already in the table with the same unique key it will delete that row before inserting.

answered Jun 06 '13 at 19:03

Bad Wolf

8,206
4
34
44

Your unique key should be a composite key of product_id and user_id, in this case it also happens to be your primary key. When the query runs, if it finds an existing product_id and user_id combination it will delete that row then perform the insert. – Bad Wolf Jun 06 '13 at 19:09
`REPLACE` has several disdvantages. Read this: [“INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”](http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update) – ypercubeᵀᴹ Jun 06 '13 at 19:13

SQL `update` if combination of keys exists in row - else create new row

3 Answers3