EXISTS problem with Delete

I have three tables (SHADES, CHANGES, AND SHADES_SUC)
Shades primary structure: Shade_ID
Changes primary structure: Old_Shade_ID
Shades_SUC primary structure Shade_ID
A record is written to Changes when a change is made to the Shades table and
some of these changes will cause a scenario where the Shades_SUC table will
hold orphaned children to the shade_ID
My delete statement is as follows:
DELETE
FROM SHADE
WHERE EXISTS
(
SELECT *
FROM CHANGES, SHADE
WHERE CHANGES.OLD_SHADE_ID = SHADE.SHADE_ID
AND CHANGES.OLD_SHADE_ID NOT IN
(SELECT SHADE_ID FROM SHADE_SUC)
)
If I highlight simply the select portion of Select * from Changes, shade...
I receive a return of 8 records - which is correct. However when I run the
entire Delete command the procedure deletes every record in the Shade table
-
I have tried numerous things - can anyone provide any direction on what I ma
y
be doing wrong here?
ThxSorry - I typed incorrectly - To Confirm... We are looking for any shades
that do not have any children in the Shades_SUC table - so that we can delet
e
them.
"Dan" wrote:

> I have three tables (SHADES, CHANGES, AND SHADES_SUC)
> Shades primary structure: Shade_ID
> Changes primary structure: Old_Shade_ID
> Shades_SUC primary structure Shade_ID
> A record is written to Changes when a change is made to the Shades table a
nd
> some of these changes will cause a scenario where the Shades_SUC table wil
l
> hold orphaned children to the shade_ID
> My delete statement is as follows:
> DELETE
> FROM SHADE
> WHERE EXISTS
> (
> SELECT *
> FROM CHANGES, SHADE
> WHERE CHANGES.OLD_SHADE_ID = SHADE.SHADE_ID
> AND CHANGES.OLD_SHADE_ID NOT IN
> (SELECT SHADE_ID FROM SHADE_SUC)
> )
> If I highlight simply the select portion of Select * from Changes, shade..
.
> I receive a return of 8 records - which is correct. However when I run th
e
> entire Delete command the procedure deletes every record in the Shade tabl
e -
> I have tried numerous things - can anyone provide any direction on what I
may
> be doing wrong here?
> Thx|||Thats correct you have to link the outer and the inner query to do this:
DELETE
FROM SHADE S
WHERE EXISTS
(
SELECT *
FROM CHANGES C
WHERE C.OLD_SHADE_ID = S.SHADE_ID
AND C.OLD_SHADE_ID NOT IN
(SELECT SHADE_ID FROM SHADE_SUC)
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Dan" <Dan@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A9B07B01-E573-49D9-95C0-637F01724BCC@.microsoft.com...
>I have three tables (SHADES, CHANGES, AND SHADES_SUC)
> Shades primary structure: Shade_ID
> Changes primary structure: Old_Shade_ID
> Shades_SUC primary structure Shade_ID
> A record is written to Changes when a change is made to the Shades table
> and
> some of these changes will cause a scenario where the Shades_SUC table
> will
> hold orphaned children to the shade_ID
> My delete statement is as follows:
> DELETE
> FROM SHADE
> WHERE EXISTS
> (
> SELECT *
> FROM CHANGES, SHADE
> WHERE CHANGES.OLD_SHADE_ID = SHADE.SHADE_ID
> AND CHANGES.OLD_SHADE_ID NOT IN
> (SELECT SHADE_ID FROM SHADE_SUC)
> )
> If I highlight simply the select portion of Select * from Changes,
> shade...
> I receive a return of 8 records - which is correct. However when I run
> the
> entire Delete command the procedure deletes every record in the Shade
> table -
> I have tried numerous things - can anyone provide any direction on what I
> may
> be doing wrong here?
> Thx|||Try,
DELETE SHADE
WHERE EXISTS
(
SELECT
*
FROM
CHANGES
left join
SHADE_SUC
on CHANGES.OLD_SHADE_ID = SHADE_SUC.SHADE_ID
WHERE
CHANGES.OLD_SHADE_ID = SHADE.SHADE_ID
and SHADE_SUC.SHADE_ID is null
);
AMB
"Dan" wrote:

> I have three tables (SHADES, CHANGES, AND SHADES_SUC)
> Shades primary structure: Shade_ID
> Changes primary structure: Old_Shade_ID
> Shades_SUC primary structure Shade_ID
> A record is written to Changes when a change is made to the Shades table a
nd
> some of these changes will cause a scenario where the Shades_SUC table wil
l
> hold orphaned children to the shade_ID
> My delete statement is as follows:
> DELETE
> FROM SHADE
> WHERE EXISTS
> (
> SELECT *
> FROM CHANGES, SHADE
> WHERE CHANGES.OLD_SHADE_ID = SHADE.SHADE_ID
> AND CHANGES.OLD_SHADE_ID NOT IN
> (SELECT SHADE_ID FROM SHADE_SUC)
> )
> If I highlight simply the select portion of Select * from Changes, shade..
.
> I receive a return of 8 records - which is correct. However when I run th
e
> entire Delete command the procedure deletes every record in the Shade tabl
e -
> I have tried numerous things - can anyone provide any direction on what I
may
> be doing wrong here?
> Thx|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
This soudns like you need to re-design the schema with a proper history
table and ON DELETE CASCADE actions.|||Receive the following:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'S'. -
Reveiwed but did not see anything obvious
"Jens Sü?meyer" wrote:

> Thats correct you have to link the outer and the inner query to do this:
>
> DELETE
> FROM SHADE S
> WHERE EXISTS
> (
> SELECT *
> FROM CHANGES C
> WHERE C.OLD_SHADE_ID = S.SHADE_ID
> AND C.OLD_SHADE_ID NOT IN
> (SELECT SHADE_ID FROM SHADE_SUC)
> )
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Dan" <Dan@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:A9B07B01-E573-49D9-95C0-637F01724BCC@.microsoft.com...
>
>|||No table alias in DELETE. Use the full name for S instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:63E6DC40-4883-4479-BAD8-78DC5DAA550B@.microsoft.com...
> Receive the following:
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near 'S'. -
> Reveiwed but did not see anything obvious
> "Jens Smeyer" wrote:
>