Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate Record and Update
Duplicate Record and Update [message #255197] Tue, 31 July 2007 00:26 Go to next message
Messages: 47
Registered: March 2007
There are duplicate records in the table, which I am able to find by
     select    <col1>
             , count(*)
from <table>
group by  <col1>
having count(*) > 1;

Is there any way to update the table expect one row from duplicate series.

I have tried Rownum which does not seems working.

Col1   col2   col3
1	xx
2       yy
1       xx
1       yy
3       abc

I am able to idetify the records as
1    xx
1    xx
1    yy

on the basis of col1

now i want to make a update as
Col1   col2   col3
1    xx
1    xx	      Dupliacte col1
1    yy       Dupliacte Col1

Can anyone help what would be the Update statement
Re: Duplicate Record and Update [message #255211 is a reply to message #255197] Tue, 31 July 2007 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 65143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many and many threads on duplicates.
Some are about deleting duplicates, just change delete to update.

Re: Duplicate Record and Update [message #255278 is a reply to message #255211] Tue, 31 July 2007 04:50 Go to previous message
Messages: 23
Registered: April 2007
Location: Noida
Junior Member

update <tab1> set col3='Duplicate Records' where rowid not in (select min(rowid) from <tab1> group by col1,col2)
Previous Topic: continue after except. raised
Next Topic: How to retrieve last record in a table...
Goto Forum:

Current Time: Fri Aug 18 13:31:39 CDT 2017

Total time taken to generate the page: 0.10828 seconds