oracle 如何动态更新前 n 行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7468067/
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
How to update top n rows dynamically?
提问by sailaja
I have a table with similar rows...like
我有一个类似行的表......就像
PartNo SerialNo Inven_Qty Status
------ -------- --------- ------
001A NULL 1 IN
001A NULL 1 IN
001A NULL 1 IN
001A NULL 1 IN
Now I want to update the rows dynamically..I have a web page where I enter PartNo and the value of items which are OUT...ex 2 items are OUT...I want to update 2 rows with Inven_Qty 0 and status as OUT....Can anyone please tell me how to do this???
现在我想动态更新行..我有一个网页，我在其中输入 PartNo 和 OUT 的项目的值...例如 2 个项目是 OUT...我想用 Inven_Qty 0 和状态更新 2 行OUT....谁能告诉我怎么做？？？
Thanks in advance.
提前致谢。
回答by vmatyi
Based on Mikael Eriksson, for Oracle:
基于 Mikael Eriksson，对于 Oracle：
update YourTable
set Inven_Qty = 0,
Status = 'OUT'
where PartNo = '001A'
and Status = 'IN'
and rownum<=3
(for 3 items to update)
（更新 3 个项目）
回答by Jan S
You can do it like this.
你可以这样做。
UPDATE table_name
SET Status = "OUT", Invent_QTY = 0,
WHERE PartNo = "<part number>" AND SerialNo = <serial number>
回答by Mikael Eriksson
This syntax works for SQL Server. Might work for other DBMS as well.
此语法适用于 SQL Server。也可能适用于其他 DBMS。
update top (2) YourTable
set Inven_Qty = 0,
Status = 'OUT'
where PartNo = '001A' and
Status = 'IN'
Another way that may be more easily translated to a some other DBMS
另一种可能更容易转换为其他 DBMS 的方式
with C as
(
select Inven_Qty,
Status,
row_number() over(order by (select 1)) as rn
from @T
where PartNo = '001A' and
Status = 'IN'
)
update C
set Inven_Qty = 0,
Status = 'OUT'
where rn <= 2
回答by pablochan
It would probably be better if you could use 'SerialNo' in the query, but if not you can try:
如果您可以在查询中使用 'SerialNo' 可能会更好，但如果不能，您可以尝试：
update <your table> set Status='OUT', Inven_Qty=0 where rowid in
(select rowid from <your table> where Status=IN and Inven_Qty=1
and PartNo=<part number> where rownum <= <some value>)
But this only works if you assume that Inven_Qty is either 1 or 0. If Inven_Qty can be greater than 1 I don't think you can even do this with just a single query.
但这仅在您假设 Inven_Qty 为 1 或 0 时才有效。如果 Inven_Qty 可以大于 1，我认为您甚至无法仅通过一个查询来做到这一点。
EDIT: This won't work if SerialNo is null. You have to have some sort of unique id for this to work. If you don't you should add one. If you're working on oracle you can use ROWID.
编辑：如果 SerialNo 为空，这将不起作用。你必须有某种独特的 id 才能工作。如果你不这样做，你应该添加一个。如果您正在使用 oracle，则可以使用 ROWID。
