MySQL Query: insert if entry is duplicate otherwise update the existing row values

Question

i have a table called bag(again):

+--------+----------+---------+----------+
| bag_id | chara_id | item_id | item_qty |
+--------+----------+---------+----------+
|      1 |        1 |       2 |       22 |
|      2 |        1 |       1 |       55 |
|      3 |        3 |       1 |        2 |
|      6 |        3 |       4 |        2 |
|      7 |        4 |       4 |        2 |
|      8 |        5 |       4 |        2 |
|      9 |        6 |       4 |        2 |
|     10 |        1 |       5 |        1 |
|     14 |        1 |       8 |        1 |
|     15 |        1 |       6 |        1 |
|     18 |        1 |       4 |        1 |
|     19 |        1 |       3 |        1 |
|     29 |        8 |       1 |        1 |
|     30 |        8 |       7 |        1 |
|     33 |        6 |       2 |        1 |
+--------+----------+---------+----------+

and i have this SQL Statement:

INSERT INTO bag(bag_id, chara_id, item_id, item_qty)VALUES(NULL, :id, :item_id,1)

after asking how to delete duplicates
wat i want to do next(to further restrict duplicates)is when a user buys an item already existing in his bag it increase the item_qty by 1 instead.

like:

if chara_id = exist and item_id exist 
   item_qty = item_qty + 1
else
   #..normal insert

if i use:

INSERT INTO bag(bag_id, chara_id, item_id)VALUES(NULL, 1, 2)

it should not insert but update the item_qty to 23 because that entry is already existing.

Lai Xin Chu · Accepted Answer · 2013-05-03 03:58:54Z

3

INSERT ... ON DUPLICATE KEY UPDATE

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

answered May 3, 2013 at 3:58

Lai Xin Chu

2,48216 silver badges29 bronze badges

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

3 Comments

Viscocent Over a year ago

ive read it, used it but it seems to be just inserting not updating even if an entry is existing

dave Over a year ago

then you need to set a unique key on the combination of chara_id and item_id

Viscocent Over a year ago

wait,.. how do i do that i only know how to set a unique undex.

John Woo · Accepted Answer · 2013-05-03 05:03:38Z

2

MySQL supports INSERT ... ON DUPLICATE KEY UPDATE

but before it will work, you need to have an unique constraint on the table. If you don't have a unique constraint yet, based on your example you are checking on two columns if the values already exists,

ALTER TABLE bag ADD CONSTRAINT tb_unique UNIQUE (chara_id, item_id)

Once it has been implemented, ON DUPLICATE KEY UPDATE will not work.

INSERT INTO bag(chara_id, item_id, item_qty)
VALUES(1, 2, 1)
ON DUPLICATE KEY UPDATE item_qty = item_qty + 1

INSERT ... ON DUPLICATE KEY UPDATE

answered May 3, 2013 at 5:03

John Woo

265k70 gold badges509 silver badges500 bronze badges

2 Comments

Viscocent Over a year ago

i have just done that a while ago, and it seems to be working fine :) thanks again, i am confused though why mysql is returning 2 rows inserted. : )

Tyler Over a year ago

I am also seeing "2 rows inserted" but it seems that only one row is actually being inserted. Is this a phpMyAdmin bug? using 4.4.12

Collectives™ on Stack Overflow

MySQL Query: insert if entry is duplicate otherwise update the existing row values

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related