split out duplicate records in Oracle table

[tilaks]

hi

i have one table in that i want to put the duplicate data in one table and original data into another table


ex main table

id name
1 xxx
1 yyyy
1 aaaa
2 fffff
2 ggg
3 kkkk
just i want put original data into one table and duplicte data in another table

table 1

id name
1 xxx
2 yyy
3 kkkk

table 2

id name
1 -----
1 -----
2 ------

[debasisdas]

try this block of code to copy the duplicate records

insert into table2 select * from table1 t where rowid>(select min(rowid) from table1 where t.id=id)

and then this block of code to delete duplicate from table1.

Delete from table1 t where rowid>(select min(rowid) from table1 where t.id=id)

hope this works for u