| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Question - Delete through tables...
A copy of this was sent to dolans_at_stripe.Colorado.EDU (Sean Dolan)
(if that email address didn't require changing)
On 26 Feb 99 16:10:50 GMT, you wrote:
>I am having trouble deleting some records from a database "cleanly". I
>could do it successfully with cursors but I'd like to make "one-line" SQL
>statements if possible. Below is the scenario (3 tables - I want to clear
>everything out of it where contractId = something):
>
>EMAILGROUPS
>ID (FK to EmailGroupUsers: GROUPID), GROUPNAME, CONTRACTID
>
>EMAILUSERTYPES
>EMAILUSERID (FK to EmailGroupUsers: ID), STATUS, PRIORITYLVL
>
>EMAILGROUPUSERS
>ID, TECHID, GROUPID
>
>If I want to delete all entries in emailusertypes that belong to Contract
>10000, how would I do it?
>
> delete from emailusertypes where exists (????) - the problem is that I
>keep deleting ALL the rows from the table instead of those that belong to a
>certain contract. Is the "exists" the right way to do it?
>
delete from emailUserTypes
where emailUserId in ( select id
from emailGroups
where contractId = 10000 )
/
if there is an index on emailGroups(contractId) and and index on emailUserTypes(emailUserId), this should be relatively efficient.
it would index scan on emailGroups by contract id and then index scan into emailUserTypes to find the rows to delete.
The where exists would look like:
delete from emailUserTypes
where exists ( select NULL
from emailGroups
where emaiGroups.id = emailUserTypes.emailUserId
and contractId = 10000 )
/
this would full scan emailUserTypes and for each row in that table, run the subquery...
>Any help would be greatly appreciated.
>
>Thanks,
>Sean Dolan
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |