SQL如何在一个命令中为一个int列增加或减少一个 [英] SQL how to increase or decrease one for a int column in one command

问题描述

我有一个订单表，其中有一个数量列。在办理入住或退房手续时，我们需要将数量列更新一个。有一种方法可以在一个操作中执行此操作，或者我们必须获取现有值，然后在其顶部添加或减去一个值。

I have an Orders table which has a Quantity column. During check in or check out, we need to update that Quantity column by one. Is there a way to do this in one action or we have to get the existing value and then add or minus one on top of it?

另一个问题是，当我们插入一个新行，我们需要检查是否有相同的数据存在然后插入如果没有，这是两个步骤，或有更好的方法来做这个？

Another question is when we insert a new row, do we need to check if same data existing then insert if not, which is two steps, or is there a better way to do this?

谢谢，

推荐答案

回答第一个问题：

UPDATE Orders SET Quantity = Quantity + 1 WHERE ...

回答第二个问题：

有几种方法可以做到这一点。因为你没有指定数据库，我将假设MySQL。

There are several ways to do this. Since you did not specify a database, I will assume MySQL.

1. INSERT INTO表SET x = 1，y = 2 ON DUPLICATE KEY UPDATE x = x + 1，y = y + 2


2. REPLACE INTO表SET x = 1，y = 2

1. INSERT INTO table SET x=1, y=2 ON DUPLICATE KEY UPDATE x=x+1, y=y+2
2. REPLACE INTO table SET x=1, y=2

他们都可以处理您的问题。但是，第一个语法允许更灵活地更新记录，而不是仅仅替换它（如第二个）。

They both can handle your question. However, the first syntax allows for more flexibility to update the record rather than just replace it (as the second one does).

请记住，必须有一个UNIQUE键定义...

Keep in mind that for both to exist, there has to be a UNIQUE key defined...

这篇关于SQL如何在一个命令中为一个int列增加或减少一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！