Delete - Exists - problem

Can anyone tell me why the following deletes all the records instead of
simply the ones returned in the "Exists" clause?
The select in the exists by itself returns 131 records, however when run in
the following context it deletes all 4474 that are in the Shades table.'
Delete FROM #TMP_SHADE WHERE EXISTS
(select DISTINCT(OLD_SHADE_ID) from #TMP_CHANGEDSHADES
where OLD_SHADE_ID NOT in (select SHADE_ID
from SHADE_SUC))
The idea is to delete any shade records in #tmp_shade where they do not
exist in shade_SUC
ThanksDan,

> Can anyone tell me why the following deletes all the records instead of
> simply the ones returned in the "Exists" clause?
Because you are not correlating the tables. If at least one row in table
#TMP_CHANGEDSHADES meet the condition, all rows from table #TMP_SHADE will b
e
deleted. It should be something like:
Delete
FROM #TMP_SHADE
WHERE EXISTS
(
select DISTINCT(OLD_SHADE_ID)
from #TMP_CHANGEDSHADES
where #TMP_CHANGEDSHADES.col1 = #TMP_SHADE.col1
and OLD_SHADE_ID NOT in (select SHADE_ID from SHADE_SUC)
)
AMB
"Dan" wrote:

> Can anyone tell me why the following deletes all the records instead of
> simply the ones returned in the "Exists" clause?
> The select in the exists by itself returns 131 records, however when run i
n
> the following context it deletes all 4474 that are in the Shades table.?
?
> Delete FROM #TMP_SHADE WHERE EXISTS
> (select DISTINCT(OLD_SHADE_ID) from #TMP_CHANGEDSHADES
> where OLD_SHADE_ID NOT in (select SHADE_ID
> from SHADE_SUC))
> The idea is to delete any shade records in #tmp_shade where they do not
> exist in shade_SUC
> Thanks
>|||Delete from X where Exists (Y)
will delete all rows in X if Y is true and nothing if Y is false.
try something like
Delete from X where Y_id in (select distinct(Y_id) from Y)
I hope you get the idea.
Regards,
Nishant
"Dan" wrote:

> Can anyone tell me why the following deletes all the records instead of
> simply the ones returned in the "Exists" clause?
> The select in the exists by itself returns 131 records, however when run i
n
> the following context it deletes all 4474 that are in the Shades table.?
?
> Delete FROM #TMP_SHADE WHERE EXISTS
> (select DISTINCT(OLD_SHADE_ID) from #TMP_CHANGEDSHADES
> where OLD_SHADE_ID NOT in (select SHADE_ID
> from SHADE_SUC))
> The idea is to delete any shade records in #tmp_shade where they do not
> exist in shade_SUC
> Thanks
>|||Great Alejandro! - Thank you for the pointer
Dan
"Alejandro Mesa" wrote:
> Dan,
>
> Because you are not correlating the tables. If at least one row in table
> #TMP_CHANGEDSHADES meet the condition, all rows from table #TMP_SHADE will
be
> deleted. It should be something like:
> Delete
> FROM #TMP_SHADE
> WHERE EXISTS
> (
> select DISTINCT(OLD_SHADE_ID)
> from #TMP_CHANGEDSHADES
> where #TMP_CHANGEDSHADES.col1 = #TMP_SHADE.col1
> and OLD_SHADE_ID NOT in (select SHADE_ID from SHADE_SUC)
> )
>
> AMB
>
>
> "Dan" wrote:
>sql