Home » SQL & PL/SQL » SQL & PL/SQL » delete conditional rows (9i)
delete conditional rows [message #419901] |
Wed, 26 August 2009 02:27  |
IIMA
Messages: 8 Registered: August 2009 Location: India
|
Junior Member |
|
|
I have a table with columns a ,b,c,d,e,f
I have to delete such rows if the col a of record 1 = col b of record 2
or col b of record 1 = col a of record 2
please post the solution to achieve this
|
|
|
|
Re: delete conditional rows [message #419919 is a reply to message #419901] |
Wed, 26 August 2009 03:28   |
IIMA
Messages: 8 Registered: August 2009 Location: India
|
Junior Member |
|
|
CREATE OR REPLACE procedure ho_und_trail1
is
type HO_UNDTRAILS_TYPE is table of HO_UNDTRAILS%rowtype;
rec_ho1 HO_UNDTRAILS_TYPE;
rec_ho2 HO_UNDTRAILS_TYPE;
count1 Number;
count2 Number;
begin
select * bulk collect into rec_ho1 from HO_UNDTRAILS;
count1 :=rec_ho1.count;
select * bulk collect into rec_ho2 from HO_UNDTRAILS;
count2 :=rec_ho2.count;
for i in 1 .. count1
loop
for j in 1 .. count2
loop
IF (i!=j) then
if (rec_ho1(i).aend_ne=rec_ho2(j).zend_ne) then
if (rec_ho1(i).zend_ne=rec_ho2(j).aend_ne) then
if (rec_ho1(i).trailid=rec_ho2(j).trailid and rec_ho1(i).J=rec_ho2(j).J and rec_ho1(i).type=rec_ho2(j).type and rec_ho1(i).AEND_PTP=rec_ho2(j).ZEND_PTP and rec_ho1(i).ZEND_PTP=rec_ho2(j).AEND_PTP) then
insert into HOUND (TRAILID,TYPE,AEND_NE,AEND_PTP,ZEND_NE,ZEND_PTP,J,K,L,M) values (rec_ho1(i).TRAILID,rec_ho1(i).TYPE,rec_ho1(i).AEND_NE,rec_ho1(i).AEND_PTP,rec_ho1(i).ZEND_NE,rec_ho1(i).ZEND_PTP,rec_ho1(i).J,rec_ho 1(i).K,rec_ho1(i).L,rec_ho1(i).M);
insert into HOUND (TRAILID,TYPE,AEND_NE,AEND_PTP,ZEND_NE,ZEND_PTP,J,K,L,M) values (rec_ho2(j).TRAILID,rec_ho2(j).TYPE,rec_ho1(i).AEND_NE,rec_ho2(j).AEND_PTP,rec_ho2(j).ZEND_NE,rec_ho2(j).ZEND_PTP,rec_ho2(j).J,rec_ho 2(j).K,rec_ho2(j).L,rec_ho2(j).M);
dbms_output.put_line('Display : Duplicate');
end if ;
end if ;
end if;
end if;
end loop;
end loop;
end;
|
|
|
|
Re: delete conditional rows [message #419924 is a reply to message #419920] |
Wed, 26 August 2009 03:43   |
IIMA
Messages: 8 Registered: August 2009 Location: India
|
Junior Member |
|
|
I have a table with columns a ,b,c,d,e,f
I have to delete such rows
if the col a of record 1 = col b of record 2
or col b of record 1 = col a of record 2
if (rec_ho1(i).aend_ne=rec_ho2(j).zend_ne) then
if (rec_ho1(i).zend_ne=rec_ho2(j).aend_ne) then
please post the solution to achieve this
|
|
|
Re: delete conditional rows [message #419927 is a reply to message #419924] |
Wed, 26 August 2009 04:05   |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
Michel Cadot wrote at Wed, 26 August 2009 13:09 |
Define "record 1", "record 2" and so on.
Please read OraFAQ Forum Guide.
Post a working Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.
|
You need to provide details that Michel asked for
|
|
|
|
Re: delete conditional rows [message #419939 is a reply to message #419928] |
Wed, 26 August 2009 04:35   |
IIMA
Messages: 8 Registered: August 2009 Location: India
|
Junior Member |
|
|
i have a table
TRAILID TYPE AEND_NE AEND_PTP ZEND_NE ZEND_PTP J K L M
1:3712 MAIN GNST1004ZH I6-OPS-2 GMWP1003ZH I6-OPS-2 3
1:3712 MAIN GMWP1003ZH I6-OPS-2 GNST1004ZH I6-OPS-2 3
1:2788 MAIN WAHW1001BU A1_SAM_STM-1#1 WBLE1002XM I7-STM-1#7 1
1:2788 MAIN WBLE1002XM I7-STM-1#7 WAHW1001BU A1_SAM_STM-1#1 1
In this record 1 = record 2 because
AEND_NE in record1 = ZEND_NE in record2
therefore record 1 and record2 are same I want to delete 2nd record.
|
|
|
|
Re: delete conditional rows [message #419949 is a reply to message #419939] |
Wed, 26 August 2009 04:57   |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
But in database there is nothing called 1st Record, 2nd record etc.., the records are just there.
In your test case all the other columns except AEND_NE and ZEND_NE are same, however, if we assume that one of the column had different value then which one of them you would like to keep in database and why?
for eg if Trail_ID is different like below then which one of them should be deleted & why?
TRAILID TYPE AEND_NE AEND_PTP ZEND_NE ZEND_PTP J K L M
3:3712 MAIN GNST1004ZH I6-OPS-2 GMWP1003ZH I6-OPS-2 3
1:3712 MAIN GMWP1003ZH I6-OPS-2 GNST1004ZH I6-OPS-2 3
[Updated on: Wed, 26 August 2009 05:00] Report message to a moderator
|
|
|
Re: delete conditional rows [message #419950 is a reply to message #419949] |
Wed, 26 August 2009 05:03   |
IIMA
Messages: 8 Registered: August 2009 Location: India
|
Junior Member |
|
|
HI,
Record 1 and record2 was just for the example .
I need to delete any of them among them .
TRAILID TYPE AEND_NE AEND_PTP ZEND_NE ZEND_PTP J K L M
3:3712 MAIN GNST1004ZH I6-OPS-2 GMWP1003ZH I6-OPS-2 3
1:3712 MAIN GMWP1003ZH I6-OPS-2 GNST1004ZH I6-OPS-2 3
there is no criteria Since all the valuse are same except aend_ne and zend_ne ,There is a duplication so I want to remove this duplication
|
|
|
|
Re: delete conditional rows [message #419957 is a reply to message #419950] |
Wed, 26 August 2009 05:17   |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
If your criteria is to keep any one of the records if aend_ne and zend_ne are same in different records, then following query will help you to identify one of the rows. You can then build on the logic to delete the records from the table
XE@SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
A VARCHAR2(10)
B VARCHAR2(10)
C NUMBER(38)
XE@SQL> select * from t;
A B C
---------- ---------- ----------
A C 18
Test New 100
GC GD 12
C C 24
J J 10
T GC 48
6 rows selected.
XE@SQL>
XE@SQL> select t1.a,t1.b,t1.c,t2.a,t2.b,t2.c from t t1,t t2 where t1.a = t2.b an
d t1.rowid != t2.rowid
2 /
A B C A B C
---------- ---------- ---------- ---------- ---------- ----------
C C 24 A C 18
GC GD 12 T GC 48
2 rows selected.
XE@SQL>
[Update] Added column C in select query above
[Updated on: Wed, 26 August 2009 05:19] Report message to a moderator
|
|
|
Re: delete conditional rows [message #419965 is a reply to message #419957] |
Wed, 26 August 2009 05:29   |
IIMA
Messages: 8 Registered: August 2009 Location: India
|
Junior Member |
|
|
I have tried the same thing but my table structure is like this
A B C
a c 18
c a 18
b d 37
d b 37
e f 22
f e 22
When i did select t1.a,t1.b,t2.a,t2.b from t t1,t t2 where t1.a = t2.b and t1.rowid
!= t2.rowid;
I found this result
A B A_1 B_1
c a a c
a c c a
f e e f
e f f e
instead I want
A B C
a c 18
b d 37
f e 22
|
|
|
|
|
|
Re: delete conditional rows [message #420000 is a reply to message #419969] |
Wed, 26 August 2009 07:55   |
IIMA
Messages: 8 Registered: August 2009 Location: India
|
Junior Member |
|
|
Could you please explain I am still not able to find the solution.
my condition is
AEND_NE in record1 = ZEND_NE in record2 and
ZEND_NE in record1 = AEND_NE in record2
or
select t1.a,t1.b,t2.a,t2.b from t t1,t t2 where t1.a = t2.b and t1.b = t2.a and t1.rowid
!= t2.rowid;
|
|
|
|
|
Re: delete conditional rows [message #420098 is a reply to message #420000] |
Thu, 27 August 2009 00:59   |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
I could come with following query to delete the records but have not completely tested it. Kindly check if it helps you and also test it thoroughly.
XE@SQL> select t1.a t1a,
2 t1.b t1b,
3 t2.a t2a,
4 t2.b t2b,
5 lag(t1.rowid) over (order by t2.rowid) t1rid,
6 t2.rowid t2rid
7 from t t1,t t2
8 where t1.a = t2.b
9 and t1.rowid ! = t2.rowid
10 /
T1A T1B T2A T2B T1RID T2RID
----- ----- ----- ----- ------------------ ------------------
c a a c AAADxgAABAAAKfyAAA
a c c a AAADxgAABAAAKfyAAB AAADxgAABAAAKfyAAB
d b b d AAADxgAABAAAKfyAAA AAADxgAABAAAKfyAAC
b d d b AAADxgAABAAAKfyAAD AAADxgAABAAAKfyAAD
f e e f AAADxgAABAAAKfyAAC AAADxgAABAAAKfyAAE
e f f e AAADxgAABAAAKfyAAF AAADxgAABAAAKfyAAF
j k q j AAADxgAABAAAKfyAAE AAADxgAABAAAKfyAAJ
7 rows selected.
XE@SQL>
XE@SQL> select * from
2 (
3 select t1.a t1a,
4 t1.b t1b,
5 t2.a t2a,
6 t2.b t2b,
7 lag(t1.rowid) over (order by t2.rowid) t1rid,
8 t2.rowid t2rid
9 from t t1,t t2
10 where t1.a = t2.b
11 and t1.rowid ! = t2.rowid
12 )
13 where t1rid = t2rid
14 /
T1A T1B T2A T2B T1RID T2RID
----- ----- ----- ----- ------------------ ------------------
a c c a AAADxgAABAAAKfyAAB AAADxgAABAAAKfyAAB
b d d b AAADxgAABAAAKfyAAD AAADxgAABAAAKfyAAD
e f f e AAADxgAABAAAKfyAAF AAADxgAABAAAKfyAAF
3 rows selected.
XE@SQL>
Ps. I could not think how to use the other standard delete duplicate query to achieve this since the duplicate values are across columns.
[Updated on: Thu, 27 August 2009 01:01] Report message to a moderator
|
|
|
Re: delete conditional rows [message #420102 is a reply to message #420098] |
Thu, 27 August 2009 01:10   |
IIMA
Messages: 8 Registered: August 2009 Location: India
|
Junior Member |
|
|
Hi All,
Thanks for the solutions I have got the solution by taking hints
(select "TRAILID","TYPE","AEND_NE","AEND_PTP","ZEND_NE","ZEND_PTP","J","K","L","M" from HO_UNDtrails) minus (SELECT "TRAILID","TYPE","AEND_NE","AEND_PTP","ZEND_NE","ZEND_PTP","J","K","L","M" FROM HO_UNDTRAILS A WHERE
rowid >
(SELECT min(rowid) FROM HO_UNDTRAILS B
WHERE
B.aend_ne = A.zend_ne
and
B.zend_ne = A.aend_ne
and
a.trailid=b.trailid
and
a.type=b.type
and
a.ZEND_PTP=b.AEND_PTP
and
a.AEND_PTP=b.ZEND_PTP
and a.J=b.J
))
|
|
|
|
|
Goto Forum:
Current Time: Tue Feb 18 13:54:19 CST 2025
|