如何执行UPSERT，以便在更新部分中可以同时使用新值和旧值 [英] How to Perform an UPSERT so that I can use both new and old values in update part

问题描述

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

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，以便在更新部分中可以同时使用新值和旧值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！