Home » SQL & PL/SQL » SQL & PL/SQL » Delete support table records...
Delete support table records... [message #36211] Fri, 09 November 2001 14:50 Go to next message
Alex Ponce
Messages: 4
Registered: November 2001
Junior Member
Hello to all... congratulations on a great resource for PL/SQL programmers!

My question is regarding this issue.

I have three tables with the following columns:

A.Number

B.Number
B.Name

C.Name

A contains multiple occurences of Number, B contains unique Number and Name values (unique key), C contains multiple occurences of Name.

I need to run a query that efficienty deletes all Name records in C only when there is at least one record in A with this Number (stored in B).

To explain better.

A.NUMBER
========
2000
2000
2000
2000

B.NUMBER B.NAME
========== =============
2000 TWO_THOUSAND

C.NAME
=======
TWO_THOUSAND
TWO_THOUSAND
TWO_THOUSAND
TWO_THOUSAND
TWO_THOUSAND

Delete all "TWO_THOUSAND" records in C given that the value 2000 exist in A.

Hope I explained correcly.

Thanks in advance.

----------------------------------------------------------------------

Report message to a moderator

Re: Delete support table records... [message #36213 is a reply to message #36211] Sun, 11 November 2001 07:35 Go to previous messageGo to next message
Robert Moy
Messages: 15
Registered: December 2000
Junior Member
Hello
I am not sure which is your table and columns names. I am using word columns for tables B and C for "TWO_THOUSANDS"

Delete
from C.Name
where C.Word=(
select B.Word
from A.Name, B.Name
where A.Name.A.Number=B.Name.B.Number)


Good Luck

----------------------------------------------------------------------

Report message to a moderator

Re: Delete support table records... [message #36224 is a reply to message #36211] Mon, 12 November 2001 06:59 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
 
delete from c
 where name in
 (select distinct b.name
    from b, a
   where b.number = a.number)


----------------------------------------------------------------------

Report message to a moderator

Previous Topic: Control Structures
Next Topic: Re: How to Retrieve CLOB withADODB Command Parameter...?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 16 05:58:46 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.