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
u263066
Messages: 47
Registered: March 2007
Member
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.

Table
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: 64103
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.

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

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: Sun Dec 04 21:00:47 CST 2016

Total time taken to generate the page: 0.15021 seconds