insert on duplicate key update for two values?

Question

My save_list table contains three columns:

id, user_id, item_id

Query

INSERT INTO save_list (user_id, item_id) VALUES (?,?)

How can I use INSERT ON DUPLICATE KEY UPDATE to update the row if both user_id and item_id exist?

Working code

INSERT INTO save_list (user_id, item_id) VALUES (?,?) ON DUPLICATE KEY UPDATE user_id = ?, item_id = ?

This question is related: http://stackoverflow.com/questions/14383503/on-duplicate-key-update-same-as-insert — jmlane, Jul 07 '15 at 19:58

score 1 · Accepted Answer · answered Jul 07 '15 at 19:53

1

Create a unique composite key across them both

ALTER TABLE tblName ADD UNIQUE unique_index777 (user_id,item_id);

answered Jul 07 '15 at 19:53

Drew

OK ... added it, but how would I use that? – Ciprian Jul 07 '15 at 20:09
Once you've added that, you can use `INSERT INTO .. ON DUPLICATE KEY UPDATE`. I just gave an answer on that [here](http://stackoverflow.com/questions/31277671/sql-replace-or-insert-into-syntax-error/31277803#31277803) which will have links to the documentation for you and such. – AdamMc331 Jul 07 '15 at 20:14
`$q = $this->db->mysqli->prepare("INSERT INTO save_list (user_id, item_id) VALUES (?,?) ON DUPLICATE KEY UPDATE");` Like this? – Ciprian Jul 07 '15 at 20:16
1

@DrewPierce didn't mean to steal the thunder from your answer, but I gave that one so recently that I had everything fresh in my mind haha. – AdamMc331 Jul 07 '15 at 20:17
1

I don't have much thunder @McAdam331 – Drew Jul 07 '15 at 20:18

score 0 · Answer 2 · edited May 28 '20 at 20:39

0

You need to have these 2 fields defined as a composite key for the update to work.

INSERT INTO foo_table(user_id, item_id, description) ft values (a,b,desc)
ON DUPLICATE KEY UPDATE ft.description=desc

Should do the trick

edited May 28 '20 at 20:39

Dharman

answered Jul 07 '15 at 19:56

Pat B

2 Answers2