INSERT with UPDATE on duplicate key

Question

I have a query which updates the table or inserts if the row does not already exist, but for some reason it just inserts all the time.

This is my table structure:

Id (primary) | uid | product_id | quantity

This is my query:

INSERT INTO my_table (uid,product_id,quantity) 
    SELECT t1.uid,?,?
    FROM checker t1
    WHERE t1.id = ?
ON DUPLICATE KEY UPDATE
    product_id = ?, quantity = quantity+?

What i want to do though is use on duplicate key if uid + product_id combination exist in the table already.

So is there a way to designate what kind of duplication to look for to update instead of insert?

zerkms · Accepted Answer · 2013-07-17 04:51:51Z

1

There is no way to distinguish between what duplication occurs.

As soon as any unique constraint is violated - it will perform ON DUPLICATE KEY UPDATE part.

For your case you just need to create unique composite key that consists of 2 fields: (uid, product_id)

answered Jul 17, 2013 at 4:51

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

zerkms Over a year ago

@Dave: what actually?

Sir Over a year ago

two fields = 1 primary key

zerkms Over a year ago

@Dave: I didn't mention primary key in my answer

Sir Over a year ago

oh sorry im so used to primary key being unique that i thought they were the same things.

Sir Over a year ago

Well i created a unique key with the two fields. And kept id as PK

|

Collectives™ on Stack Overflow

INSERT with UPDATE on duplicate key

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related