Home » SQL & PL/SQL » SQL & PL/SQL » Delete duplicate records without Rowid and drop (2 Merged) (Oracle 10G Pl/Sql)
Delete duplicate records without Rowid and drop (2 Merged) [message #550243] Sat, 07 April 2012 12:32 Go to next message
bonvivant
Messages: 14
Registered: April 2012
Location: Noida
Junior Member
I want to delete the duplicate records from a table without using Below 2 methods:

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;


3> ????????? Shocked Shocked Shocked

Please suggest......

Re: Delete duplicate records without Rowid and drop [message #550245 is a reply to message #550243] Sat, 07 April 2012 12:39 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Delete duplicate records without Rowid and drop [message #550246 is a reply to message #550245] Sat, 07 April 2012 12:45 Go to previous messageGo to next message
bonvivant
Messages: 14
Registered: April 2012
Location: Noida
Junior Member
THis is the general question and i cannot put the script output.
Pl. help me to resolve this
Re: Delete duplicate records without Rowid and drop [message #550247 is a reply to message #550246] Sat, 07 April 2012 12:53 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior 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);

how can there be duplicate Primary Keys?
Re: Delete duplicate records without Rowid and drop [message #550250 is a reply to message #550246] Sat, 07 April 2012 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many ways, the most efficient of them are described in our SQL FAQ.

Regards
Michel
Re: Delete duplicate records without Rowid and drop [message #550265 is a reply to message #550250] Sun, 08 April 2012 00:34 Go to previous messageGo to next message
bonvivant
Messages: 14
Registered: April 2012
Location: Noida
Junior Member
Thanks Michel,, But i dont want to use ROWID anywhere in my delete statement. I want the solution to delete the duplicate rows without using ROWID...

BlackSwan,, There is no duplicate primary keys, Its a self join...
Re: Delete duplicate records without Rowid and drop [message #550267 is a reply to message #550265] Sun, 08 April 2012 00:41 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
>Thanks Michel,, But i dont want to use ROWID anywhere in my delete statement. I want the solution to delete the duplicate rows without using ROWID...

How do you differentiate "duplicate" row; other than by ROWID?
Re: Delete duplicate records without Rowid and drop [message #550273 is a reply to message #550267] Sun, 08 April 2012 00:46 Go to previous messageGo to next message
bonvivant
Messages: 14
Registered: April 2012
Location: Noida
Junior Member
We can identify the duplicate records using Group by clause, But without ROWID is it not possible to delete the duplicate records????
This was asked to me somewhere and i am still searching for the answer... Surprised
Re: Delete duplicate records without Rowid and drop [message #550276 is a reply to message #550273] Sun, 08 April 2012 00:48 Go to previous messageGo to next message
bonvivant
Messages: 14
Registered: April 2012
Location: Noida
Junior Member
eagrly waiting for the solution
Re: Delete duplicate records without Rowid and drop [message #550277 is a reply to message #550273] Sun, 08 April 2012 00:51 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
>This was asked to me somewhere and i am still searching for the answer...
If you don't know where, then why does any answer matter to you?
Let me ask you to please answer this question, what causes object to fall upwards?
Please post you answer after you learn it.
Re: Delete duplicate records without Rowid and drop [message #550278 is a reply to message #550277] Sun, 08 April 2012 00:54 Go to previous messageGo to next message
bonvivant
Messages: 14
Registered: April 2012
Location: Noida
Junior Member
I think this is a good question to discuss thats why i put this in the thread.
BlackSwan sir, If you feel like this is worthless then i will delete this post.
Re: Delete duplicate records without Rowid and drop [message #550280 is a reply to message #550278] Sun, 08 April 2012 00:58 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
bonvivant wrote on Sat, 07 April 2012 22:54
I think this is a good question to discuss thats why i put this in the thread.
BlackSwan sir, If you feel like this is worthless then i will delete this post.


How do you differentiate "duplicate" row; other than by ROWID?
Re: Delete duplicate records without Rowid and drop [message #550281 is a reply to message #550280] Sun, 08 April 2012 01:05 Go to previous messageGo to next message
bonvivant
Messages: 14
Registered: April 2012
Location: Noida
Junior Member
I am searching the answer for this thead. Once i get the solution i Will post the same.. Thanks all..
Re: Delete duplicate records without Rowid and drop [message #550282 is a reply to message #550278] Sun, 08 April 2012 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If ALL columns and pseudo-columns are equals, you can't distinguish the two:
SQL> create table t (val integer);

Table created.

SQL> insert into t values(1);

1 row created.

SQL>  insert into t values(1);

1 row created.

SQL> select * from t where rownum=1;
       VAL
----------
         1

1 row selected.

SQL> /
       VAL
----------
         1

1 row selected.

Is the second select giving the same row than the first one?
If you can answer that, Oracle can.
If you can't, Oracle can't.

Regards
Michel
Re: Delete duplicate records without Rowid and drop [message #550283 is a reply to message #550282] Sun, 08 April 2012 01:14 Go to previous messageGo to next message
bonvivant
Messages: 14
Registered: April 2012
Location: Noida
Junior Member
Thanks Michel for the strong support.
Re: Delete duplicate records without Rowid and drop [message #550294 is a reply to message #550273] Sun, 08 April 2012 02:20 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
From this thread: http://www.orafaq.com/forum/t/180359/96705/
(just not to pollute it with more out-of-original-topic posts than it currently has)
bonvivant's reply to the post below
FLYBOY, But suppose i have only one column with duplicate data then how to delete duplicate without using rowid, Is it possible?????

flyboy's original post
Michel gave you logical reason for using ROWID there; maybe you should try to understand it.

I have nothing to say more. Maybe just will repeat myself: did you at least try to understand it?
Re: Delete duplicate records without Rowid and drop [message #550295 is a reply to message #550294] Sun, 08 April 2012 02:30 Go to previous messageGo to next message
bonvivant
Messages: 14
Registered: April 2012
Location: Noida
Junior Member
thanks flyboy..
Re: Delete duplicate records without Rowid and drop [message #550297 is a reply to message #550295] Sun, 08 April 2012 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And to answer "is it popssible without rowid and drop table", it is easy to find one that STRICTLY answer this with what you posted.

Regards
Michel
Re: Delete duplicate records without Rowid and drop (2 Merged) [message #594440 is a reply to message #550243] Wed, 28 August 2013 08:58 Go to previous messageGo to next message
hsinam
Messages: 29
Registered: February 2012
Junior Member
Hi,
Please use this for your solution:-

delete from test
where name in(select name from test
group by name
having count(name)>1)
Re: Delete duplicate records without Rowid and drop (2 Merged) [message #594441 is a reply to message #594440] Wed, 28 August 2013 09:05 Go to previous messageGo to next message
pablolee
Messages: 2617
Registered: May 2007
Location: Scotland
Senior Member
1. bit of a zombie thread (over a year old)
2. it looks like you misunderstood the question - this will delete both rows that are duplicated, the requirement would be to keep one of the duplicated rows
Re: Delete duplicate records without Rowid and drop (2 Merged) [message #594442 is a reply to message #594440] Wed, 28 August 2013 09:06 Go to previous message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
@hsinam:

1) Please read and follow How to use [code] tags and make your code easier to read? As you have been asked to many times. Repeatedly ignoring moderators requests for you to obey the rules of the forum like everybody else is just rude.
2) This thread is over a year old, hopefully the OP will have solved it by now.
3) Your code will delete both copies of all duplicates. The OP wanted one copy of each duplicate to be left after the delete. So your code is not a solution.
Previous Topic: PLS-00597
Next Topic: Value for the closest or equal date
Goto Forum:
  


Current Time: Fri Sep 19 06:49:48 CDT 2014

Total time taken to generate the page: 0.08827 seconds