CODEWITHSUNDEEP
sqloracle-database
Sakthi
Sakthi

Reputation: 103

How to update single row of multiple duplicate rows in Oracle SQL

I have this table:

group_id | invoice_amt|insert_date
---------+------------+------
23        1002         10/8/2018
23        1002         10/8/2018
23        1003         11/8/2018
21        1004         12/8/2018

When I using following query,

select distinct group_id, invoice_amt, insert_date 
from table

I'm getting the last 3 rows. But I need all four rows. For that I need to update one of top rows rows by changing date. Group_id and invoice_amt should not change. How to do that.?

Upvotes: 0

Views: 4005

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Something like this?

update t
    set insert_date = insert_date + 1
    where rowid > (select min(rowid)
                   from t t2
                   where t2.group_id = t.group_id and t2.invoice_amt = t.invoice_amt
                  );

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

If you need all four rows, just remove DISTINCT, because it forces unique record set:

select group_id, invoice_amt, insert_date from table

If you need to change date on all duplicates but one value and have no unique identifier within a table itself you can use internal Oracle pseudocolumn ROWID:

update table t
set insert_date = < your date here >
where rowid < (
  select max(rowid)
  from table t2
  where t.group_id = t2.group_id
    and t.invoice_amt = t2.invoice_amt
  );

Upvotes: 3

Related Questions