CODEWITHSUNDEEP
sqlsql-server
Dekso
Dekso

Reputation: 561

SQL Update only duplicate row

table_paysched

id |  ilno | acctno
1  |  1    | 001
2  |  1    | 001
3  |  2    | 001
4  |  1    | 002
5  |  1    | 002
6  |  0    | 003
7  |  1    | 003

I want to update the duplicate 1s ilno to 0. Thanks

Wanted Result

id |  ilno | acctno
1  |  0    | 001
2  |  1    | 001
3  |  2    | 001
4  |  0    | 002
5  |  1    | 002
6  |  0    | 003
7  |  1    | 003

I'm using SQL Server

Upvotes: 1

Views: 72

Answers (2)

Vecchiasignora
Vecchiasignora

Reputation: 1315

use this

with temp as (
    select *, 
    row_number() over (partition by acctno order by id desc) row_num,
    count() over (partition by acctno ) cnt_num
    from myTable
)

update myTable m
set m.ilno = 0
where exists (select 1 from temp t where m.id = t.id and t.row_num = 1 and cnt_num > 1)

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You can use CTE with window function row_number to do the update:

with cte as (
    select *, row_number() over (partition by acctno order by id desc) rn
    from t
    where ilno = 1
)
update cte
set ilno = 0
where rn <> 1;

Demo

Upvotes: 1

Related Questions