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

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.

From stackoverflow $pageUsers[$entry.posts[0].author].name

• Will this work for you:

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

  From Brian Schmitt

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

  From JosephStyons

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

  From Aaron Smith

• 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.

  From IronGoofy

• 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!!!

  Jaap Coomans : So... flag his answer as the accepted solution
  From $pageUsers[$post.author].name

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

  From $pageUsers[$post.author].name

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

  From $pageUsers[$post.author].name