如果有重复的记录，则更新同一表的列 [英] Update a column of same table if there is duplicate records

问题描述

如果表中的行重复，我需要更新一列.

I need to update a column if the rows are duplicate in the table.

在这种情况下，如果有3行重复，那么我们必须将两行标记为错误，将一行标记为成功.

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.

虚拟数据可能像

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;

此查询给出重复的行 但是这里的问题是，我需要将2行acc_num 43532111的DONE列更新为错误"，并将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 ...

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);

然后更新5行，即除非重复项外的所有重复行.

Then it updates 5 rows i.e. all duplicate rows except non-dups.

但是在我们的情况下，它只应将3行2重复(对于acc_num = 43532111)和1重复(对于acc_num = 623352777)视为错误"

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'

实际表有大约35列，所以有什么方法我们不需要为group by子句写每个列名吗?

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?

我正在使用---

Oracle Database 12c企业版12.1.0.2.0版-64位生产

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

请不要尝试拨打上述仅用于测试目的的电话号码！

And please do not try to call on above numbers they are only for test purpose!

推荐答案

您可以使用分析功能 rowid 获取行:

You can use the analytical function row_number() and rowid to get the rows:

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)

这篇关于如果有重复的记录，则更新同一表的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！