Re: Subquery SQL Statemebnt (Delete)

On 4 Aug 98 17:57:10 GMT, dolans_at_stripe.Colorado.EDU (Sean Dolan) wrote:

> In straight SQL terms, I am trying to delete from a table where by I do=
 a subquery on another table first: EX:
>
>delete from shhd_owner.incident_journal where id = (Select ij.ID
>FROM SHHD_OWNER.INCIDENT_JOURNAL ij, SHHD_OWNER.INCIDENTS i WHERE
>i.CONTRACT_ID=10000 AND (ij.INCIDENT_ID=i.ID));
>
>The problem is that the subquery returns multiple rows and the delete =
cannot function. Am I missing something or am I going to have to make a = PL/SQL procedure with using a cursor and fetch?
>
>Thanks,
>Sean

Hi Sean,

I hope I understand correctly what you're trying to do: Delete those records from the incident journal where the contract_id of the related incident is 10000, with ij.incident_id being a mandatory foreign key referencing the primary key incident_id on the incidents table.

If that's correct you could just replace the single row subquery you use currently with a multirow subquery, i.e. replace the '=' with 'IN'. With regard to performance it is probably better to use an equivalent statement with a correlated subquery like this one:

DELETE FROM shhd_owner.incident_journal ij  WHERE 10000 =

      ( SELECT i.contract_id
          FROM shhd_owner.incidents i
         WHERE i.incident_id = ij.incident_id);

or this:

DELETE FROM shhd_owner.incident_journal ij  WHERE EXISTS

      ( SELECT 1
          FROM shhd_owner.incidents i
         WHERE i.incident_id = ij.incident_id
           AND i.contract_id = 10000);

HTH,
Peter

--
Peter Schneider
peter.schneider_at_okay.net Received on Wed Aug 05 1998 - 19:35:21 CDT