MySQL查询:如果条目重复则插入，否则更新现有行值 [英] MySQL Query: insert if entry is duplicate otherwise update the existing row values

问题描述

我有一个名为 bag (再次)的表:

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 |
+--------+----------+---------+----------+

我有这个 SQL语句:

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

询问如何删除重复项后
我想做的下一个步骤(进一步限制重复项)是当用户购买包中已经存在的商品时，将item_qty加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.

点赞:

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

如果我使用:

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

它不应插入，但将item_qty更新为23，因为该条目已经存在.

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

推荐答案

MySQL支持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)

一旦实施，ON DUPLICATE KEY UPDATE将不起作用.

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

这篇关于MySQL查询:如果条目重复则插入，否则更新现有行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！