database SQL如何在一个命令中为int列增加或减少一个
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/973380/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL how to increase or decrease one for a int column in one command
提问by 5YrsLaterDBA
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?
我有一个 Orders 表，它有一个 Quantity 列。在签入或签出期间，我们需要将该 Quantity 列更新一。有没有办法在一个动作中做到这一点，或者我们必须获得现有值，然后在它上面加上或减去一个？
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?
另一个问题是，当我们插入新行时，是否需要检查是否存在相同的数据，如果不存在则插入，这是两个步骤，还是有更好的方法来做到这一点？
thanks,
谢谢，
回答by gahooa
To answer the first:
回答第一个：
UPDATE Orders SET Quantity = Quantity + 1 WHERE ...
To answer the second:
回答第二个：
There are several ways to do this. Since you did not specify a database, I will assume MySQL.
有几种方法可以做到这一点。由于您没有指定数据库，我将假设 MySQL。
INSERT INTO table SET x=1, y=2 ON DUPLICATE KEY UPDATE x=x+1, y=y+2REPLACE INTO table SET x=1, y=2
INSERT INTO table SET x=1, y=2 ON DUPLICATE KEY UPDATE x=x+1, y=y+2REPLACE 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).
他们都可以处理你的问题。但是，第一种语法允许更灵活地更新记录，而不是仅仅替换它（就像第二种语法那样）。
Keep in mind that for both to exist, there has to be a UNIQUE key defined...
请记住，要使两者都存在，必须定义一个 UNIQUE 键...
回答by paxdiablo
The single-step answer to the first question is to use something like:
第一个问题的一步答案是使用类似的东西：
update TBL set CLM = CLM + 1 where key = 'KEY'
That's very much a single-instruction way of doing it.
这在很大程度上是一种单指令方式。
As for the second question, you shouldn't need to resort to DBMS-specific SQL gymnastics (like UPSERT) to get the result you want. There's a standard method to do update-or-insert that doesn't require a specific DBMS.
至于第二个问题，您不需要求助于特定于 DBMS 的 SQL 体操（如UPSERT）来获得您想要的结果。有一种不需要特定 DBMS 进行更新或插入的标准方法。
try:
insert into TBL (key,val) values ('xyz',0)
catch:
do nothing
update TBL set val = val + 1 where key = 'xyz'
That is, you try to do the creation first. If it's already there, ignore the error. Otherwise you create it with a 0 value.
也就是说，您尝试先进行创建。如果它已经存在，请忽略该错误。否则，您将使用 0 值创建它。
Then do the update which will work correctly whether or not:
然后进行更新，无论是否正常工作：
- the row originally existed.
- someone updated it between your insert and update.
- 该行最初存在。
- 有人在您的插入和更新之间更新了它。
It's not a single instruction and yet, surprisingly enough, it's how we've been doing it successfully for a long long time.
这不是一个单一的指令，然而，令人惊讶的是，它是我们长期以来成功地做到这一点的方式。
回答by msvcyc
If my understanding is correct, updates should be pretty simple. I would just do the following.
如果我的理解是正确的，更新应该非常简单。我只会做以下事情。
UPDATE TABLE SET QUANTITY = QUANTITY + 1 and
UPDATE TABLE SET QUANTITY = QUANTITY - 1 where QUANTITY > 0
You may need additional filters to just update a single row instead of all the rows.
您可能需要额外的过滤器来更新单行而不是所有行。
For inserts, you can cache some unique id related to your record locally and check against this cache and decide whether to insert or not. The alternative approach is to always insert and check for PK violation error and ignore since this is a redundant insert.
对于插入，您可以在本地缓存一些与您的记录相关的唯一 ID，并检查此缓存并决定是否插入。另一种方法是始终插入并检查 PK 违规错误并忽略，因为这是一个冗余插入。
回答by Daniel Brückner
UPDATE Orders Order
SET Order.Quantity = Order.Quantity - 1
WHERE SomeCondition(Order)
As far as I know there is no build-in support for INSERT-OR-UPDATE in SQL. I suggest to create a stored procedure or use a conditional query to achiev this. Hereyou can find a collection of solutions for different databases.
据我所知，SQL 中没有对 INSERT-OR-UPDATE 的内置支持。我建议创建一个存储过程或使用条件查询来实现这一点。在这里，您可以找到针对不同数据库的一系列解决方案。
回答by dotjoe
to answer the second:
回答第二个：
make the column unique and catch the exception if it's set to the same value.
使列唯一并捕获异常（如果它设置为相同的值）。
回答by brian chandley
@dotjoe It is cheaper to update and check @@rowcount, do an insert after then fact.
@dotjoe 更新和检查@@rowcount 更便宜，然后在事实之后进行插入。
Exceptions are expensive && updates are more frequent
例外是昂贵的&&更新更频繁
Suggestion: If you want to be uber performant in your DAL, make the front end pass in a unique ID for the row to be updated, if null insert.
建议：如果你想在你的 DAL 中表现得更好，让前端传递一个唯一的 ID 来更新行，如果插入为空。
The DALs should be CRUD, and not need to worry about being stateless.
DAL 应该是 CRUD，不需要担心是无状态的。
If you make it stateless, With good indexes, you will not see a diff with the following SQL vs 1 statement. IF (select top 1 * form x where PK=@ID) Insert else update
如果你让它无状态，使用良好的索引，你不会看到以下 SQL vs 1 语句的差异。IF (select top 1 * form x where PK=@ID) 插入 else 更新
