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.?
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
);
DISTINCT is must is, sometime real duplicate entries can be made and to avoid them.
insert_date column to be type timestamp with time zone and your problem is resolved for future. Then perform the update. Also, rethink about duplicates - why real duplicates can be made? Then are they really duplicates?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
);
