Hello -

I'm new to this website and also somewhat new to utilizing SQL DB2. I am stuck in a situation where i need to use SQL to delete selected rows out of one table by meeting criteria from other tables. This is what i have written so far:

delete from
jhcterry1.atpantrn
where
exists

(select
b.athash
from
jhcterry1.atcardupd a join jhcterry1.atcard b
on a.athash = b.athash join atmhsts518.atpantrn c
on
b.athash = c.atxhash
where
a.atcdst <> 'O' and b.atdtis = 093011)

This query is supposed to delete ONLY records from atpantrn if a.atcdst <> 'O' and b.atdtis = 093011, but for some reason when i run it, it is deleting every record inside the file. What am I doing wrong?? Please help if you can.

There is no relation between tables in where clause and the target table of delete(i.e. jhcterry1.atpantrn).
So, all rows in jhcterry1.atpantrn would be deleted if exists a row satisfying where condition,
or no row would be deleted if not exists a row satisfying where condition.

I couldn't see the name(or alias) of the target table(i.e. jhcterry1.atpantrn) in exists subquery.

target table:
jhcterry1.atpantrn

tables in exists subquery:
jhcterry1.atcardupd --- same schema name, different table name.
jhcterry1.atcard --- same schema name, different table name.
atmhsts518.atpantrn --- same table name, different schema name.

but it is deleting rows out of jhcterry1.atpantrn even when the where condition isn't true. The entire file is being deleted. So jhcterry1.atpantrn needs to be in the sub select? how so?? and where?? Thanks

I want to repeat this
where a row has no relation with the target table.
It may or may not exist independent from the status of target.

Add a correlation name to the target table, like...
delete from
jhcterry1.atpantrn AS a
where
...

then reference the correlation name(i.e. a) in where clause.

Ok i'm with you now on understanding that if even one condition of the where clause is met the entire table is whipped out, but i'm stil not sure "syntax" wise how to incorporate the correlation name. Should I say from: a then join to atcardupd, atcard, and atmhsts518.atpantrn??

delete from
jhcterry1.atpantrn as a
where
exists
(select

from
jhcterry1.atcardupd a join jhcterry1.atcard b
on a.athash = b.athash join atmhsts518.atpantrn c
on
b.athash = c.atxhash
where
a.atcdst <> 'O' and b.atdtis = 093011)

You are using a correlation name "a" for jhcterry1.atcardupd in where clause.
So, use another correlation name for the target table, like...
delete from
jhcterry1.atpantrn as t
where
...

then add conditions for t.<column name1>(and t.<column name2> and so on, if neccesary) in where clause.

lets say i use z for my correlation table. I still don't understand how i incorporate that into the where...what am i comparision "z" to in the where clause? what am i wanting to compare in the other tables against the delete table? z.(column name1 = ??) exists?

delete from
jhcterry1.atpantrn as z
where
exists
(select

from
jhcterry1.atcardupd a join jhcterry1.atcard b
on a.athash = b.athash join atmhsts518.atpantrn c
on
b.athash = c.atxhash
where
a.atcdst <> 'O' and b.atdtis = 093011)

<column name1>, <column name2>, so on are column names of table jhcterry1.atpantrn.

If you still don't understand, ask to(or wait answer from) another people.

This will need to identify the rows that you wish to delete, obviously, since you don't want to delete all of them.

Do you have any examples of something similar i could reference? I'm still not clear as to how to reference the correspondence table in the where clause. Thanks

Explain in a human language (preferably English) how you would identify the rows to be deleted. It's easy from there.