Re: Subquery SQL Statemebnt (Delete)

Peter Schneider a écrit:

> 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));

I don't exactly understand what you want to do! Do you want to delete ALL rows that have duplicate values on INCIDENT_ID when CONTRACT_ID = 1000? If this is the case try this

DELETE FROM shhd_owner.incident_journal I1 WHERE I1.contract_id = 1000 AND EXISTS
(SELECT *
 FROM shhd_owner.incident_journal I2
WHERE I1.incident_id = I2.incident_id AND I1.rowid != I2.rowid)

The condition on rowid is very important if you don't want to simply delete all rows with CONTARCT_ID = 1000!

If you want to keep one of these rows you must change the inequality by a strictly greater than or a strictly less than operator.

> 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);

What if there is multiple rows in the subquery? If not, that would be equivalent to :

DELETE FROM shhd_owner.incident_journal WHERE contract_id=10000

> 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);
>

This is equivalent to :

DELETE FROM shhd_owner.incident_journal WHERE contract_id=10000; Received on Thu Aug 13 1998 - 04:51:54 CDT