|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Delete duplicate records without Rowid and drop [message #550294 is a reply to message #550273] |
Sun, 08 April 2012 02:20 |
flyboy
Messages: 1903 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 belowFLYBOY, But suppose i have only one column with duplicate data then how to delete duplicate without using rowid, Is it possible?????
flyboy's original postMichel 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 (2 Merged) [message #645099 is a reply to message #645080] |
Thu, 26 November 2015 08:06 |
|
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.
|
|
|
Re: Delete duplicate records without Rowid and drop (2 Merged) [message #645100 is a reply to message #645099] |
Thu, 26 November 2015 08:21 |
cookiemonster
Messages: 13917 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 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
cookiemonster wrote on Thu, 26 November 2015 09:21It'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 #645172 is a reply to message #550243] |
Sat, 28 November 2015 01:58 |
|
sanbak
Messages: 3 Registered: November 2015
|
Junior Member |
|
|
Hi guys,
Here's my 2cents to this almost antique but intriguing thread
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 #645182 is a reply to message #645174] |
Sat, 28 November 2015 04:40 |
|
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 #645188 is a reply to message #645185] |
Sat, 28 November 2015 05:25 |
|
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
|
|
|
|
|