Home » SQL & PL/SQL » SQL & PL/SQL » How to identify one row from duplicate rows
How to identify one row from duplicate rows [message #659475] Wed, 18 January 2017 11:04 Go to next message
asliyanage
Messages: 60
Registered: January 2017
Member
There are two rows with contain(duplicate data) same data. I need to delete first one .So how can i identify first row ? can i use rowid for this?
Re: How to identify one row from duplicate rows [message #659476 is a reply to message #659475] Wed, 18 January 2017 11:24 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
rows in a table are like balls in a basket.
Which ball in a basket is the first ball?

If the rows are duplicates, why does it matter which row gets removed?

If you don't want duplicates, then why did you allow the second row to go into the table?
Re: How to identify one row from duplicate rows [message #659477 is a reply to message #659476] Wed, 18 January 2017 11:44 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
No. I need to know whether we can uniquely identify a row from duplicates?
Re: How to identify one row from duplicate rows [message #659478 is a reply to message #659477] Wed, 18 January 2017 11:47 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
asliyanage wrote on Wed, 18 January 2017 09:44
No. I need to know whether we can uniquely identify a row from duplicates?
PLEASE answer the questions below.


If the rows are duplicates, why does it matter which row gets removed?

If you don't want duplicates, then why did you allow the second row to go into the table?

two rows are either unique or they are duplicate.
They are mutually exclusive.
Re: How to identify one row from duplicate rows [message #659479 is a reply to message #659478] Wed, 18 January 2017 11:53 Go to previous messageGo to next message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
Every row has a unique rowid, so try the following (use the correct table name and columns that determine your duplicate rows.

delete from my_table  a
where
   a.rowid <>
     (select max(rowid) from my_table b
      where b.key1 = a.key1
      and b.key2 = a.key2
      and b.key3 = a.key3); 

If the entire row is duplicated then you can do something like
create table rebuild_my_table as
select distinct *
from my_table;

truncate table my_table;

insert into my_table select * from rebuild_my_table;

commit;

drop refresh_my_table;

[Updated on: Wed, 18 January 2017 11:54]

Report message to a moderator

Re: How to identify one row from duplicate rows [message #660275 is a reply to message #659479] Tue, 14 February 2017 01:43 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
Thanks everyone
Re: How to identify one row from duplicate rows [message #660281 is a reply to message #660275] Tue, 14 February 2017 01:57 Go to previous message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Explain what you learned from this topic to help future readers.

Previous Topic: What is Unknown in logical operator
Next Topic: REGEXP to find the comma position
Goto Forum:
  


Current Time: Sun Feb 25 06:56:28 CST 2018

Total time taken to generate the page: 0.01000 seconds