UPDATE statement in Oracle using SQL or PL/SQL to update first duplicate row ONLY

Question

Hi,

I'm looking for an UPDATE statement where it will update a single duplicate row only and remain the rest (duplicate rows) intact as is, using ROWID or something else or other elements to utilize in Oracle SQL or PL/SQL?

Here is an example duptest table to work with:

CREATE TABLE duptest (ID VARCHAR2(5), NONID VARCHAR2(5));

• run one INSERT INTO duptest VALUES('1','a');

• run four (4) times INSERT INTO duptest VALUES('2','b');

Also, the first duplicate row has to be updated (not deleted), always, whereas the other three (3) have to be remained as is!

Thanks a lot, Val.

Answer 1

Will this work for you:

update duptest 
set nonid = 'c'
WHERE ROWID IN (SELECT   MIN (ROWID)
                              FROM duptest 
                          GROUP BY id, nonid)

Answer 2

This worked for me, even for repeated runs.

--third, update the one row
UPDATE DUPTEST DT
SET DT.NONID = 'c'
WHERE (DT.ID,DT.ROWID) IN(
                         --second, find the row id of the first dup
                         SELECT 
                           DT.ID
                          ,MIN(DT.ROWID) AS FIRST_ROW_ID
                         FROM DUPTEST DT
                         WHERE ID IN(
                                    --first, find the dups
                                    SELECT ID
                                    FROM DUPTEST
                                    GROUP BY ID
                                    HAVING COUNT(*) > 1
                                    )
                         GROUP BY
                           DT.ID
                         )

Answer 3

I think this should work.

UPDATE DUPTEST SET NONID = 'C'
WHERE ROWID in (
    Select ROWID from (
        SELECT ROWID, Row_Number() over (Partition By ID, NONID order by ID) rn
    ) WHERE rn = 1
)

Answer 4

I know that this does not answer your initial question, but there is no key on your table and the problem you have adressing a specific row results from that.

So my suggestion - if the specific application allows for it - would be to add a key column to your table (e.g. REAL_ID as INTEGER).

Then you could find out the lowest id for the duplicates

select min (real_id) 
from duptest
group by (id, nonid)

and update just these rows:

update duptest
set nonid = 'C'
where real_id in (<select from above>)

I'm sure the update statement can be tuned somewhat, but I hope it illustrates the idea.

The advantage is a "cleaner" design (your id column is not really an id), and a more portable solution than relying on the DB-specific versions of rowid.

Answer 5

Kogus,

You are the boss!!!

I’ve tried so many options, variations, here and there and your’s was the key, the bull's eye!

Thank you very much!!!

Philadelphia PHILLIES - 2008 WORLD CHAMPIANS!!!

Answer 6

UPDATE duptest SET nonid = 'c' WHERE nonid = 'b' AND rowid = (SELECT min(rowid) FROM duptest WHERE nonid = 'b');

Answer 7

JosephStyons, Tons of Thanks to You.Your solution worked for me after 3hrs battle..