Home » SQL & PL/SQL » SQL & PL/SQL » delete conditional rows (9i)
delete conditional rows [message #419901] Wed, 26 August 2009 02:27 Go to next message
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 #419910 is a reply to message #419901] Wed, 26 August 2009 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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.

Regards
Michel
Re: delete conditional rows [message #419919 is a reply to message #419901] Wed, 26 August 2009 03:28 Go to previous messageGo to next message
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 #419920 is a reply to message #419901] Wed, 26 August 2009 03:31 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
What is this?
Can't you explain?

regards,
Delna
Re: delete conditional rows [message #419924 is a reply to message #419920] Wed, 26 August 2009 03:43 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #419928 is a reply to message #419901] Wed, 26 August 2009 04:06 Go to previous messageGo to next message
sharjeelshafqat
Messages: 33
Registered: August 2009
Location: Pakistan
Member

sorry explain properly
or read records section properly

thanks
sr. oracle programmer
sharjeel
Re: delete conditional rows [message #419939 is a reply to message #419928] Wed, 26 August 2009 04:35 Go to previous messageGo to next message
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 #419947 is a reply to message #419939] Wed, 26 August 2009 04:52 Go to previous messageGo to next message
sharjeelshafqat
Messages: 33
Registered: August 2009
Location: Pakistan
Member

you cannot explain properly
send me your forms and database DMP file
thanks
Re: delete conditional rows [message #419949 is a reply to message #419939] Wed, 26 August 2009 04:57 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #419952 is a reply to message #419949] Wed, 26 August 2009 05:05 Go to previous messageGo to next message
sharjeelshafqat
Messages: 33
Registered: August 2009
Location: Pakistan
Member

READ EXEC_SQL in this forum
Re: delete conditional rows [message #419957 is a reply to message #419950] Wed, 26 August 2009 05:17 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #419969 is a reply to message #419950] Wed, 26 August 2009 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Since all the valuse are same except aend_ne and zend_ne ,There is a duplication

So group them by all the columns but this one and this is a standard "delete duplicates" question many times asked and answered.

Regards
Michel
Re: delete conditional rows [message #419970 is a reply to message #419947] Wed, 26 August 2009 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sharjeelshafqat wrote on Wed, 26 August 2009 11:52
you cannot explain properly
send me your forms and database DMP file
thanks

What does this mean?
Who is talking about forms?

Regards
Michel

Re: delete conditional rows [message #419971 is a reply to message #419952] Wed, 26 August 2009 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sharjeelshafqat wrote on Wed, 26 August 2009 12:05
READ EXEC_SQL in this forum

Once again what does this mean? What is EXEC_SQL in SQL*Plus?

Regards
Michel

Re: delete conditional rows [message #420000 is a reply to message #419969] Wed, 26 August 2009 07:55 Go to previous messageGo to next message
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 #420012 is a reply to message #420000] Wed, 26 August 2009 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Since all the valuse are same except aend_ne and zend_ne ,There is a duplication

Quote:
my condition is

AEND_NE in record1 = ZEND_NE in record2 and
ZEND_NE in record1 = AEND_NE in record2

Is this consistent?
Your condition is on these columns or all columns but them?

Regards
Michel
Re: delete conditional rows [message #420068 is a reply to message #419965] Wed, 26 August 2009 22:29 Go to previous messageGo to next message
sharjeelshafqat
Messages: 33
Registered: August 2009
Location: Pakistan
Member

I think you want to delete the record from table.
If value of column1 match value of column2.

IS IT TRUE
Re: delete conditional rows [message #420098 is a reply to message #420000] Thu, 27 August 2009 00:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
))
Re: delete conditional rows [message #420105 is a reply to message #420102] Thu, 27 August 2009 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: delete conditional rows [message #420106 is a reply to message #420102] Thu, 27 August 2009 01:29 Go to previous message
sharjeelshafqat
Messages: 33
Registered: August 2009
Location: Pakistan
Member

your welcome.

Previous Topic: Using online option during index rebuild.
Next Topic: Set Operations on Columns (not Tables)?
Goto Forum:
  


Current Time: Sun Dec 11 08:01:14 CST 2016

Total time taken to generate the page: 0.04504 seconds