如何执行 UPSERT 以便我可以在更新部分使用新旧值

How to Perform an UPSERT so that I can use both new and old values in update part(如何执行 UPSERT 以便我可以在更新部分使用新旧值)

问题描述

愚蠢但简单的例子:假设我有一个项目"表，我在其中保存收到的项目总数.

Stupid but simple example: Assume I have a table 'Item' where I keeps totals of the items that receive.

Item_Name              Items_In_Stock

项目名称是这里的主键.当我收到数量为 X 的物品 A 时，如何实现以下目标.

Item name is primary key here. How to i achieve the following when ever I receive item A in quantity X.

如果该项目不存在，我为项目 A 插入一个新记录并将库存项目设置为 X，如果存在库存项目为 Y 的记录，则库存项目的新值是 (X +是)

If the item does not exist, I insert a new recored for Item A and set the items in stock to X and if there exists a record where items in stock was Y then the new value in items in stock is (X + Y)

INSERT INTO `item`
(`item_name`, items_in_stock)
VALUES( 'A', 27)
ON DUPLICATE KEY UPDATE
`new_items_count` = 27 + (SELECT items_in_stock where item_name = 'A' )

我的问题是我的实际表中有多个列.更新部分写多个select语句好不好?

My problem is that i have multiple column in my actual table. Is it a good idea to write multiple select statements in the update part?

当然我可以用代码来做，但有更好的方法吗?

Of course I can do it in code but is there a better way?

推荐答案

正如我在评论中提到的，您不必执行子选择来引用导致 ON DUPLICATE KEY 触发的行.因此，在您的示例中，您可以使用以下内容:

As mentioned in my comment, you don't have to do the subselect to reference to the row that's causing ON DUPLICATE KEY to fire. So, in your example you can use the following:

INSERT INTO `item`
(`item_name`, items_in_stock)
VALUES( 'A', 27)
ON DUPLICATE KEY UPDATE
`new_items_count` = `new_items_count` + 27

请记住，大多数事情都非常简单，如果您发现自己将本应该简单的事情变得过于复杂，那么您很可能做错了 :)

Remember that most things are really simple, if you catch yourself overcomplicating something that should be simple then you are most likely doing it the wrong way :)

这篇关于如何执行 UPSERT 以便我可以在更新部分使用新旧值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！