SQL SQL如何只更新第一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21258250/
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 Update only first Row
提问by Lorn
I'm working with PostgreSQL. I have table with some elements. In last column there is 'Y' or 'N' letter. I need command which Select only first that match (I mean where last column is 'N') and change it on 'Y'.
我正在使用 PostgreSQL。我有一些元素的表。在最后一列有“Y”或“N”字母。我需要只选择第一个匹配的命令（我的意思是最后一列是'N'）并在'Y'上更改它。
My idea:
我的点子：
UPDATE Table SET Checked='Y'
WHERE (SELECT Checked FROM Table WHERE Checked='N' ORDER BY ID LIMIT 1) = 'N'
But it changes 'N' to 'Y' in every row.
但它在每一行中都将“N”更改为“Y”。
回答by Craig Ringer
Why it didn't work
为什么它不起作用
Others have answered the how, but you really need to understand whythis was wrong:
其他人已经回答了how，但您确实需要了解为什么这是错误的：
UPDATE Table SET Checked='Y' WHERE (
SELECT Checked FROM Table WHERE Checked='N' ORDER BY ID LIMIT 1
) = 'N'
SQL evaluates step by step in a well-defined order. In this case, the subquery evaluates first because it's uncorrelated, i.e. it doesn't refer to any variables from the outer query.
SQL 以明确定义的顺序逐步计算。在这种情况下，子查询首先评估，因为它是不相关的，即它不引用外部查询中的任何变量。
The subquery finds the first row in idorder where 'Checked' is 'N', and as the SELECT list contains the field Checked, that means the subquery will be substituted for the value N. Effectively it does nothing (except it might be NULLinstead of Nif no rows matched).
子查询id按 'Checked' 为 'N' 的顺序查找第一行，并且由于 SELECT 列表包含字段Checked，这意味着子查询将替换值N。实际上，它什么都不做（除非没有匹配的行，否则它可能会NULL代替N）。
So now you have:
所以现在你有：
UPDATE Table SET Checked='Y' WHERE 'N' = 'N';
Starting to see what went wrong?
开始明白出了什么问题？
'N' = 'N'is always going to be true. So the WHEREclause is always true, and you might as well have written an unconstrained update.
'N' = 'N'总是会是真的。因此该WHERE子句始终为真，您不妨编写一个不受约束的更新。
UPDATE Table SET Checked='Y';
How to fix it
如何修复
You're trying to find the first row where checkedis nand set checkedto y. You need to connectthese two parts using the primary key. Find the idyou want to update, then use that to constrain the update.
您正在尝试找到第一行 where checkedisn并设置checked为y。您需要使用主键连接这两个部分。找到id您要更新的，然后使用它来限制更新。
Others have already written the text of that query, so I won't repeat it here. Hopefully you now understand those answers, though.
其他人已经写了那个查询的文本，所以我不会在这里重复。不过，希望你现在明白这些答案。
回答by Akshay
Here is query
这是查询
UPDATE Table SET Checked='Y'
WHERE ID =(SELECT ID FROM Table WHERE Checked='N' ORDER BY ID LIMIT 1)
回答by AntonK
Mention that if you have the concurrent requests you need to handle with transactions, for example -
提到如果您有需要处理事务的并发请求，例如 -
UPDATE Table SET Checked='Y' WHERE ID =(SELECT ID FROM Table WHERE Checked='N' ORDER BY ID LIMIT 1 FOR UPDATE SKIP LOCKED)
if you want to skip locked rows
如果你想跳过锁定的行
回答by Ulan Ibraev
Example of mssql:
mssql 示例：
UPDATE Table SET Checked='Y'
WHERE ID =(SELECT top 1 ID FROM Table WHERE Checked='N')
Example of oracle:
甲骨文示例：
UPDATE Table SET Checked='Y'
WHERE ID =(SELECT ID FROM Table WHERE Checked='N' and rownum<2)
