oracle 如果有重复记录，更新同一张表的一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36541411/
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 a column of same table if there is duplicate records
提问by mradul
I need to update a column if the rows are duplicate in the table.
如果表中的行重复，我需要更新一列。
Here the scenario is like if there are 3 rows which are duplicate then we have to mark two rows as error and one row as success.
这里的场景就像有 3 行是重复的，那么我们必须将两行标记为错误，将一行标记为成功。
Dummy data could be like
虚拟数据可能像
create table test_dup (acc_num number, tel_num number, imsi number, done varchar2(20));
insert into test_dup values (43532111, 9874554422, 58944235, null);
insert into test_dup values (43532111, 9874554422, 58944235, null);
insert into test_dup values (43532111, 9874554422, 58944235, null);
insert into test_dup values (43532333, 9845781554, 265454522, null);
insert into test_dup values (623352777, 9928123457, 89455422, null);
insert into test_dup values (623352777, 9928123457, 89455422, null);
select acc_num, tel_num, imsi
from test_dup
group by acc_num, tel_num, imsi having count(acc_num) > 1;
This query gives rows which are duplicate But the question here is that i need to update the DONE column as 'error' for 2 rows of acc_num 43532111 and 1 row as 'success' so for that if i use a update statement like ...
这个查询给出了重复的行 但这里的问题是，我需要将 2 行 acc_num 43532111 的 DONE 列更新为“错误”，将 1 行更新为“成功”，因此如果我使用更新语句，例如...
update test_dup
set done = 'error'
where (acc_num,tel_num, imsi) in (select acc_num, tel_num, imsi
from test_dup
group by acc_num, tel_num, imsi
having count(acc_num) > 1);
Then it updates 5 rows i.e. all duplicate rows except non-dups.
然后它更新 5 行，即除非重复行之外的所有重复行。
but in our case it should only udpate 3 rows 2 duplicate for acc_num = 43532111 and 1 duplicate for acc_num = 623352777 as 'error'
但在我们的例子中，它应该只将 acc_num = 43532111 的 3 行 2 重复和 acc_num = 623352777 的 1 重复作为“错误”
The real table has around 35 columns, so is there any way that we do not need to write each column name for group by clause?
真正的表大约有 35 列，那么有什么方法不需要为 group by 子句编写每个列名？
I am using ---
我在用 - -
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Oracle Database 12c 企业版 12.1.0.2.0 版 - 64 位生产
And please do not try to call on above numbers they are only for test purpose!
并且请不要尝试拨打以上号码，它们仅用于测试目的！
回答by Mottor
You can use the analytical function row_number()and rowidto get the rows:
您可以使用分析函数row_number()和rowid来获取行：
UPDATE test_dup
SET done = 'error'
WHERE ROWID IN (SELECT ROWID
FROM (SELECT acc_num, tel_num, imsi, ROW_NUMBER () OVER (PARTITION BY acc_num, tel_num, imsi ORDER BY acc_num) AS ROW_NUMBER FROM test_dup)
WHERE ROW_NUMBER > 1)
