Oracle 中的 UPDATE 语句使用 SQL 或 PL/SQL 仅更新第一个重复行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/244671/
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
UPDATE statement in Oracle using SQL or PL/SQL to update first duplicate row ONLY
提问by Brian Schmitt
I'm looking for an UPDATE statement where it will update a single duplicate row only and remain the rest (duplicate rows) intact as is, using ROWID or something else or other elements to utilize in Oracle SQL or PL/SQL?
我正在寻找一个 UPDATE 语句，它只更新单个重复行并保持其余（重复行）不变，使用 ROWID 或其他东西或其他元素在 Oracle SQL 或 PL/SQL 中使用？
Here is an example duptest table to work with:
这是一个可以使用的示例 duptest 表：
CREATE TABLE duptest (ID VARCHAR2(5), NONID VARCHAR2(5));
run one
INSERT INTO duptest VALUES('1','a');run four (4) times
INSERT INTO duptest VALUES('2','b');
运行一个
INSERT INTO duptest VALUES('1','a');运行四 (4) 次
INSERT INTO duptest VALUES('2','b');
Also, the first duplicate row has to be updated (not deleted), always, whereas the other three (3) have to be remained as is!
此外，必须始终更新（而不是删除）第一个重复行，而其他三 (3) 行必须保持原样！
Thanks a lot, Val.
非常感谢，瓦尔。
回答by Brian Schmitt
Will this work for you:
这对你有用吗：
update duptest
set nonid = 'c'
WHERE ROWID IN (SELECT MIN (ROWID)
FROM duptest
GROUP BY id, nonid)
回答by Brian Schmitt
UPDATE duptest
SET nonid = 'c'
WHERE nonid = 'b'
AND rowid = (SELECT min(rowid)
FROM duptest
WHERE nonid = 'b');
回答by JosephStyons
This worked for me, even for repeated runs.
这对我有用，即使是重复运行也是如此。
--third, update the one row
UPDATE DUPTEST DT
SET DT.NONID = 'c'
WHERE (DT.ID,DT.ROWID) IN(
--second, find the row id of the first dup
SELECT
DT.ID
,MIN(DT.ROWID) AS FIRST_ROW_ID
FROM DUPTEST DT
WHERE ID IN(
--first, find the dups
SELECT ID
FROM DUPTEST
GROUP BY ID
HAVING COUNT(*) > 1
)
GROUP BY
DT.ID
)
回答by Aaron Smith
I think this should work.
我认为这应该有效。
UPDATE DUPTEST SET NONID = 'C'
WHERE ROWID in (
Select ROWID from (
SELECT ROWID, Row_Number() over (Partition By ID, NONID order by ID) rn
) WHERE rn = 1
)
回答by Thorsten
I know that this does not answer your initial question, but there is no key on your table and the problem you have adressing a specific row results from that.
我知道这不能回答您最初的问题，但是您的表上没有键，并且您解决特定行的问题由此产生。
So my suggestion - if the specific application allows for it - would be to add a key column to your table (e.g. REAL_ID as INTEGER).
所以我的建议 - 如果特定应用程序允许 - 将向您的表中添加一个键列（例如 REAL_ID 为 INTEGER）。
Then you could find out the lowest id for the duplicates
然后你可以找出重复项的最低 id
select min (real_id)
from duptest
group by (id, nonid)
and update just these rows:
并仅更新这些行：
update duptest
set nonid = 'C'
where real_id in (<select from above>)
I'm sure the update statement can be tuned somewhat, but I hope it illustrates the idea.
我确信更新语句可以稍微调整一下，但我希望它说明了这个想法。
The advantage is a "cleaner" design (your id column is not really an id), and a more portable solution than relying on the DB-specific versions of rowid.
优点是“更干净”的设计（您的 id 列不是真正的 id），并且是比依赖特定于数据库的 rowid 版本更可移植的解决方案。
