OraFAQ Forum: SQL & PL/SQL » duplicate record - delete

Home » SQL & PL/SQL » SQL & PL/SQL » duplicate record - delete (oracle 10g )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

duplicate record - delete [message #550266]

Sun, 08 April 2012 00:38

babhuko
Messages: 49
Registered: December 2011
Location: Chennai

Member

Hi all,

I have a requirement to delete duplicate records. For example,if the below query retrieves count of duplicate records along with the column values.

select col2,col3,col4,col5,col6,col7,count(*) from table
group by
col2,col3,col4,col5,col6,col7 
having count(*) > 1;

I would like to retain only one record with max(col1) which is a surrogate key and other records should be deleted.

How to retain one record in a duplicate record set based on max of certain column.

Thanks,
babhuko

Report message to a moderator

Re: duplicate record - delete [message #550270 is a reply to message #550266]

Sun, 08 April 2012 00:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_eliminate_duplicates_rows_from_a_table.3F

Report message to a moderator

Re: duplicate record - delete [message #550274 is a reply to message #550270]

Sun, 08 April 2012 00:48

bonvivant
Messages: 14
Registered: April 2012
Location: Noida

Junior Member

1> delete from table_1 a where row_id not in (select min(row_id) from Table_1 b where a.PK=b.PK);

2>
Insert into Table_2 select distinct * from Table_1;
Drop table Table_1;
Rename Table_2 to Table_1;

Report message to a moderator

Re: duplicate record - delete [message #550284 is a reply to message #550274]

Sun, 08 April 2012 01:16

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is useless to repeat what is inside the link Blackswan provided, and even useless when you post less than what has already been provided or when is something wrong:
Are you sure your point 2 delete the duplicates in the meaning OP posted?
You must understand the answer you post before posting it.

By the way, how to do it without using rowid and create table?

Regards
Michel

[Updated on: Sun, 08 April 2012 01:17]

Report message to a moderator

Re: duplicate record - delete [message #550287 is a reply to message #550284]

Sun, 08 April 2012 01:21

bonvivant
Messages: 14
Registered: April 2012
Location: Noida

Junior Member

12

[Updated on: Sun, 08 April 2012 01:30]

Report message to a moderator

Re: duplicate record - delete [message #550288 is a reply to message #550287]

Sun, 08 April 2012 01:31

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No, the correct answer is 42.

Regards
Michel

Report message to a moderator

Re: duplicate record - delete [message #550289 is a reply to message #550288]

Sun, 08 April 2012 01:54

bonvivant
Messages: 14
Registered: April 2012
Location: Noida

Junior Member

Point 2 is not a good pactice because the access to users will be removed once table is dropped.
Dear Michel i am in the thread to find out the solution but you are asking my question to me.

My question was is it possible to delete the duplicate records without using ROWID or DROP table?? If you dont know about the solution then please dont ask to me.. I will share once i get it

Report message to a moderator

Re: duplicate record - delete [message #550290 is a reply to message #550289]

Sun, 08 April 2012 01:58

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Point 2 is not a good pactice because the access to users will be removed once table is dropped.

What I said is point 2 (as you posred it) is NOT a solution to OP's question. Maybe you should read the link we provided you.

Quote:

If you dont know about the solution then please dont ask to me..

I gave you the answer to this question.

Regards
Michel

Report message to a moderator

Re: duplicate record - delete [message #550292 is a reply to message #550289]

Sun, 08 April 2012 02:04

flyboy
Messages: 1903
Registered: November 2006

Senior Member

@bonvivant: Did you not mistake this thread for the one you started: http://www.orafaq.com/forum/t/180358/0/
Michel gave you logical reason for using ROWID there; maybe you should try to understand it.

Because, the original post in this thread is slightly different - the row is supposed to be "duplicate" if not all columns are the same. So, rows may distinguished by the entire set of columns column instead of ROWID. The question is, what shall be the result when there are multiple "records with max(col1)". Anyway, those rows may be deleted by any method BlackSwan proposed afterwards.

Report message to a moderator

Re: duplicate record - delete [message #550293 is a reply to message #550292]

Sun, 08 April 2012 02:07

bonvivant
Messages: 14
Registered: April 2012
Location: Noida

Junior Member

Thanks michel , I got your point for Point 2.

FLYBOY, But suppose i have only one column with duplicate data then how to delete duplicate without using rowid, Is it possible?????

Report message to a moderator

Re: duplicate record - delete [message #550296 is a reply to message #550293]

Sun, 08 April 2012 02:43

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Is it possible?????

Yes you gave one (or rather the principle of one).

Regards
Michel

Report message to a moderator

Re: duplicate record - delete [message #550316 is a reply to message #550296]

Sun, 08 April 2012 11:11

babhuko
Messages: 49
Registered: December 2011
Location: Chennai

Member

Hi all,

Here is the solution.


delete from table1 a
where a.col1 < (select max(b.col1) from table1 b 
where
a.col2 = b.col2 and
a.col3 = b.col3 and
a.col4 = b.col4 and
a.col5 = b.col5 and 
a.col6 = b.col6 and
a.col7 = b.col7
)

Here i will retain only one record with the max(col1) value. Thanks.

Report message to a moderator

Re: duplicate record - delete [message #550318 is a reply to message #550316]

Sun, 08 April 2012 11:39

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Here is the solution

This is ONE solution, not THE one.
There are many others, and the most efficient ones are listed in the link Blackswan provided and I advise you to read.

Thanks for the feedback.
Regards
Michel

Report message to a moderator

Re: duplicate record - delete [message #550319 is a reply to message #550318]

Sun, 08 April 2012 11:50

babhuko
Messages: 49
Registered: December 2011
Location: Chennai

Member

**********

[Updated on: Sun, 08 April 2012 11:55]

Report message to a moderator

Re: duplicate record - delete [message #550320 is a reply to message #550319]

Sun, 08 April 2012 12:14

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Just for completeness: when there will be multiple rows with "max(col1)", this statement will leave all of them (as I stated in my previous post). If you wanted to leave only one (arbitrary) row, you would need to run another DELETE statement in addition - the same one as in the link BlackSwan posted.

Report message to a moderator

Previous Topic:	String Between two Strings
Next Topic:	join condition - one column vs many

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 01:52:01 CDT 2024