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: 24687
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: 24687
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: 63158
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: 24687
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: 24687
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: 24687
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: 63158
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: 1825
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: 63158
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: 2782
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 messageGo to next message
cookiemonster
Messages: 12177
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.
Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645073 is a reply to message #550243] Wed, 25 November 2015 12:43 Go to previous messageGo to next message
Bighnaraj@OXG
Messages: 2
Registered: November 2015
Junior Member
I think the bellow code might be help for your query. I just taken the example of emp table

Delete From EMP
Where EMPNO In(
Select b from(
Select
Decode(row_number()over(Partition by sal order by sal) ,1,'D', empno) b
From emp)
where upper(b)=lower(b))

Please let me know if you need more information regarding the same.

Laughing Laughing Laughing
Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645074 is a reply to message #645073] Wed, 25 November 2015 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 63158
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No it will not help, it is plain wrong (and even silly) and I don't see anything funny in your post, what make you laugh?

In addition, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

[Updated on: Wed, 25 November 2015 13:58]

Report message to a moderator

Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645075 is a reply to message #645074] Wed, 25 November 2015 12:53 Go to previous messageGo to next message
BlackSwan
Messages: 24687
Registered: January 2009
Senior Member
>Please let me know if you need more information regarding the same.
Do you think OP has been waiting 3.5 YEARS for your incorrect "solution"?

You never get a second chance to make a bad first impression.
Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645078 is a reply to message #645074] Wed, 25 November 2015 13:20 Go to previous messageGo to next message
Bighnaraj@OXG
Messages: 2
Registered: November 2015
Junior Member
Hi Michael,

My apologies and thank you for caching up my mistake,

Bye the way i am new for this forum and going forward,I will try to do my label best.


Thnaks and regard

Bighnaraj
Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645079 is a reply to message #645078] Wed, 25 November 2015 14:11 Go to previous messageGo to next message
Bill B
Messages: 1337
Registered: December 2004
Senior Member
Since this is no longer a zombie thread (lol) then the following follows his rules in the first note of the issue.

create Table_2 as select distinct * from Table_1;
truncate table Table_1;
insert into table_1 select * from table_2;

No table drops used.
Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645080 is a reply to message #645079] Wed, 25 November 2015 17:58 Go to previous messageGo to next message
cookiemonster
Messages: 12177
Registered: September 2008
Location: Rainy Manchester
Senior Member
There will be a drop if you tidy up after you're done.
Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645099 is a reply to message #645080] Thu, 26 November 2015 08:06 Go to previous messageGo to next message
Rishab_le_noob
Messages: 12
Registered: November 2015
Location: Kolkata
Junior Member
Ok as we are digging up old skeletons, here's my 2 cents on this.
The question never said it would need only SQL to delete these rows, so my solution will be "WHERE CURRENT OF".
This can ID a record without using a ROWID identifier.

create table test_master
(name   varchar2(10),
id     number(10));



insert all 
into test_master values('A',10)
into test_master values('A',10)
into test_master values('A',10)
into test_master values('B',20)
into test_master values('B',20)
into test_master values('C',15)
select * from dual;



declare
cursor c1 is
select name,row_number() over (partition by name,id order by name)  rwm
from test_master 
for update;

x1 c1%ROWTYPE;
begin

open c1;
loop
exit when c1%NOTFOUND;
fetch c1 into x1;

dbms_output.put_line(x1.name||'-'||x1.rwm);
if x1.rwm!=1
then
delete from test_master where current of c1;
end if;

end loop;
close c1;
commit;
end;
/





Dirty but it works. Embarassed
Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645100 is a reply to message #645099] Thu, 26 November 2015 08:21 Go to previous messageGo to next message
cookiemonster
Messages: 12177
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's less code with a FOR LOOP:
DECLARE

  CURSOR c1 IS
  SELECT name,row_number() over (partition by name,id order by name)  rwm
  FROM test_master 
  FOR UPDATE;

BEGIN

FOR x1 IN c1 LOOP

  dbms_output.put_line(x1.name||'-'||x1.rwm);
  IF x1.rwm != 1 THEN
  
    DELETE FROM test_master 
    WHERE CURRENT OF c1;

  END IF;

END LOOP;

COMMIT;

END;
/
Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645106 is a reply to message #645100] Thu, 26 November 2015 10:12 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2398
Registered: January 2010
Location: Connecticut, USA
Senior Member
cookiemonster wrote on Thu, 26 November 2015 09:21
It's less code with a FOR LOOP:


Well, it is less code but still same sub-optimal performance. Checking row number in PL/SQL creates a situation where we fetch all table rows while delete just duplicates. For example, table has 10,000,000 rows and 2 duplicate rows. Code will fetch all 10,000,000 rows but delete only 2 duplicate rows. Filtering rows in SQL using:

with t as (
           select  name,
                   row_number() over (partition by name,id order by 1)  rwm
             from  test_master
          )
select  *
  from  t
  where rwm != 1
  for update 


will result in fetching 2 rows and deleting 2 rows. Also, I believe, WHERE CURRENT OF under the hood is same ROWID.

SY.
Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645107 is a reply to message #645106] Thu, 26 November 2015 10:57 Go to previous messageGo to next message
cookiemonster
Messages: 12177
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well yes I probably should have pointed out it's not the fastest approach, I just really dislike cursor loop constructs like that.
Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645108 is a reply to message #645106] Thu, 26 November 2015 12:44 Go to previous messageGo to next message
Rishab_le_noob
Messages: 12
Registered: November 2015
Location: Kolkata
Junior Member
Using your query I get this error

SQL> with t as (
               select  name,
                       row_number() over (partition by name,id order by 1)  rwm

                 from  test_master
              )
  select  *
  from  t
  where rwm != 1
  for update ;
  from  t
        *
ERROR at line 7:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.


Am using 11g XE at home.
Is this a limitation of XE or is it just not possible?
Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645109 is a reply to message #645108] Thu, 26 November 2015 13:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2398
Registered: January 2010
Location: Connecticut, USA
Senior Member
Oops, you right - subquery factoring or in-line view will prevent using FOR UPDATE.

SY.
icon12.gif  Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645172 is a reply to message #550243] Sat, 28 November 2015 01:58 Go to previous messageGo to next message
sanbak
Messages: 3
Registered: November 2015
Junior Member
Hi guys,

Here's my 2cents to this almost antique but intriguing thread Razz
Using this approach you can remove duplicates for one ID at a time.
Don't have the time to refine it currently but am pretty confident that
using a combination of co-related sub-queries this can be looped for n number of
IDs existing in the table.

drop table test1;
/

create table test1
(id number(10),
C2 varchar2(10));
/

insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(2,'B');
/
insert into test1 values(2,'B');
/
insert into test1 values(2,'B');
/
insert into test1 values(2,'B');
/
insert into test1 values(2,'B');
/
insert into test1 values(2,'B');
/

commit;

select * from test1;
/

delete from test1
where id = 2 and
rownum <> (select max(rownum) from test1 where id = 2);
/

Output of above script -
Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.

ID C2
---------- ----------
1 A
1 A
1 A
1 A
1 A
1 A
1 A
1 A
1 A
2 B
2 B
2 B
2 B
2 B
2 B


15 rows selected.
5 rows deleted.






Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645174 is a reply to message #645172] Sat, 28 November 2015 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63158
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Using this approach you can remove duplicates for one ID at a time.


Which is completely useless for the topic.

Quote:
Don't have the time to refine it currently but am pretty confident that
using a combination of co-related sub-queries this can be looped for n number of
IDs existing in the table.


Prove it and post this statement otherwise your post is not more than noise.

Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645182 is a reply to message #645174] Sat, 28 November 2015 04:40 Go to previous messageGo to next message
sanbak
Messages: 3
Registered: November 2015
Junior Member
Hi Michel,

Still looking into if it's possible with co-related sub-queries, however It's a no brainer with PL/SQL.
Wonder what took it so looooong...

drop table test1;
/

create table test1
(id number(10),
C2 varchar2(10));
/

insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(2,'B');
/
insert into test1 values(2,'B');
/
insert into test1 values(2,'B');
/
insert into test1 values(2,'B');
/
insert into test1 values(2,'B');
/
insert into test1 values(2,'B');
/

commit;

select * from test1;
/

--Well with PL/SQL it's a fairly simple thing to do -

begin
for X in (select distinct id from test1)
loop
delete from test1
where id = X.id and
rownum <> (select max(rownum) from test1 where id = X.id);
end loop;
COMMIT;
for X in (select * from test1)
loop
dbms_output.put_line(X.id||'-'||X.C2);
end loop;
end;
/

Script Output -
Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.

ID C2
---------- ----------
1 A
1 A
1 A
1 A
1 A
1 A
1 A
1 A
1 A
2 B
2 B
2 B
2 B
2 B
2 B


15 rows selected.
PL/SQL procedure successfully completed.

1-A
2-B




Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645185 is a reply to message #645182] Sat, 28 November 2015 04:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63158
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.

Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645188 is a reply to message #645185] Sat, 28 November 2015 05:25 Go to previous messageGo to next message
sanbak
Messages: 3
Registered: November 2015
Junior Member
Apologies...
Reposting with code tags -

Hi Michel,

Still looking into if it's possible with co-related sub-queries, however It's a no brainer with PL/SQL.
Wonder what took it so looooong...

drop table test1;
/

create table test1
(id number(10),
C2 varchar2(10));
/

insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(1,'A');
/
insert into test1 values(2,'B');
/
insert into test1 values(2,'B');
/
insert into test1 values(2,'B');
/
insert into test1 values(2,'B');
/
insert into test1 values(2,'B');
/
insert into test1 values(2,'B');
/

commit;

select * from test1;
/

--Well with PL/SQL it's a fairly simple thing to do -

begin
for X in (select distinct id from test1)
loop
delete from test1
where id = X.id and
rownum <> (select max(rownum) from test1 where id = X.id);
end loop;
COMMIT;
for X in (select * from test1)
loop
dbms_output.put_line(X.id||'-'||X.C2);
end loop;
end;
/

Script Output -
Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.

ID C2
---------- ----------
1 A
1 A
1 A
1 A
1 A
1 A
1 A
1 A
1 A
2 B
2 B
2 B
2 B
2 B
2 B


15 rows selected.
PL/SQL procedure successfully completed.

1-A
2-B

Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645189 is a reply to message #645188] Sat, 28 November 2015 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 63158
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Wonder what took it so looooong


For each row of test1 (first scan) you scan the whole table to get all rows with the id and you scan the whole table to get the max.
Look at previous PL/SQL code to get faster codes which are anyway slower than SQL one.

Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645192 is a reply to message #645109] Sat, 28 November 2015 07:25 Go to previous messageGo to previous message
Michel Cadot
Messages: 63158
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Solomon Yakobson wrote on Thu, 26 November 2015 20:25
Oops, you right - subquery factoring or in-line view will prevent using FOR UPDATE.

SY.


It is not subquery factoring, it is ROW_NUMBER:
SQL> with t as (
  2             select /*+ materialize */ name
  3               from  test_master
  4            )
  5  select  *
  6    from  t
  7    for update
  8  /
NAME
----------
A
A
A
B
B
C

SQL> select name from (select /*+ materialize */ * from test_master where id > 0) for update;
NAME
----------
A
A
A
B
B
C



Previous Topic: Closing cursors - but its does not? Error: maximum open cursors exceeded
Next Topic: sql - transpose
Goto Forum:
  


Current Time: Wed May 25 08:49:08 CDT 2016

Total time taken to generate the page: 0.11698 seconds