Deleting Records

I have a table with a load of orphaned records (I know... poor design)
I'm trying to get rid of them, but I'm having a brain cramp.

I need to delete all the records from the table "Floor_Stock" that
would be returned by this select statement:

SELECT FLOOR_STOCK.PRODUCT, FLOOR_STOCK.SITE
FROM PRODUCT_MASTER INNER JOIN
FLOOR_STOCK ON PRODUCT_MASTER.PRODUCT =
FLOOR_STOCK.PRODUCT LEFT OUTER JOIN
BOD_HEADER ON FLOOR_STOCK.PRODUCT =
BOD_HEADER.PRODUCT AND FLOOR_STOCK.SITE =
BOD_HEADER.SITE
WHERE (BOD_HEADER.BOD_INDEX IS NULL) AND
(PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's'))

I was thinking along the lines of:

DELETE FROM FLOOR_STOCK INNER JOIN
(SELECT FLOOR_STOCK. PRODUCT, FLOOR_STOCK.SITE
FROM PRODUCT_MASTER INNER JOIN
FLOOR_STOCK ON PRODUCT_MASTER. PRODUCT =
FLOOR_STOCK.PRODUCT
LEFT OUTER JOIN BOD_HEADER ON FLOOR_STOCK. PRODUCT =
BOD_HEADER. PRODUCT AND FLOOR_STOCK.SITE = BOD_HEADER.SITE
WHERE (BOD_HEADER.BOD_INDEX IS NULL) AND
(PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's'))) F ON
FLOOR_STOCK. PRODUCT = F. PRODUCT
AND FLOOR_STOCK.SITE = F.SITE

... but Sql Server just laughs at me: "Incorrect Syntax near the keyword INNER"Did you try:
DELETE FROM FLOOR_STOCK
WHERE (FLOOR_STOCK.PRODUCT, FLOOR_STOCK.SITE) IN
(SELECT FLOOR_STOCK.PRODUCT, FLOOR_STOCK.SITE
FROM PRODUCT_MASTER
INNER JOIN FLOOR_STOCK
ON PRODUCT_MASTER. PRODUCT = FLOOR_STOCK.PRODUCT
LEFT OUTER JOIN BOD_HEADER
ON FLOOR_STOCK. PRODUCT = BOD_HEADER. PRODUCT
AND FLOOR_STOCK.SITE = BOD_HEADER.SITE
WHERE (BOD_HEADER.BOD_INDEX IS NULL) AND
(PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's')))
:eek:|||:( Sql Server doesn't like that either.
I didn't think you could use the IN clause on multiple fields.
Thanks anyway!|||You could CONCATENATE the columns:
WHERE FLOOR_STOCK.PRODUCT+FLOOR_STOCK.SITE IN (
SELECT FLOOR_STOCK.PRODUCT+FLOOR_STOCK.SITE FROM...
:D|||if the purpose of the LEFT OUTER join was to find unmatched rows by checking BOD_INDEX IS NULL, then i think this will do it --
delete from FLOOR_STOCK
where exists
( select *
from PRODUCT_MASTER
where PRODUCT
= FLOOR_STOCK.PRODUCT
and PROD_TYPE
in ('f', 'n', 'k', 'b', 'l', 's')
)
and not exists
( select *
from BOD_HEADER
where PRODUCT
= FLOOR_STOCK.PRODUCT
and SITE
= FLOOR_STOCK.SITE
)|||DELETE FS
FROM
FLOOR_STOCK FS
INNER JOIN (
SELECT
FS1.PRODUCT,
FS1.SITE
FROM
PRODUCT_MASTER PM
INNER JOIN FLOOR_STOCK FS1 ON PM.PRODUCT = FS1.PRODUCT
LEFT OUTER JOIN BOD_HEADER BH ON FS1.PRODUCT = BH.PRODUCT
AND FS1.SITE = BH.SITE
WHERE
BH.BOD_INDEX IS NULL
AND PM.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's')) FS2 ON
FS.PRODUCT = FS2.PRODUCT
AND FS.SITE = FS2.SITE|||Did you cut and paste directly from your code? Because your second statement (the delete one) had odd spaces inserted in it that would certainly cause syntax errors.

Does this give you an error, and if so, on what line?

DELETE
FROM FLOOR_STOCK
INNER JOIN
(SELECT FLOOR_STOCK.PRODUCT,
FLOOR_STOCK.SITE
FROM PRODUCT_MASTER
INNER JOIN FLOOR_STOCK ON PRODUCT_MASTER.PRODUCT = FLOOR_STOCK.PRODUCT
LEFT OUTER JOIN BOD_HEADER
ON FLOOR_STOCK.PRODUCT = BOD_HEADER.PRODUCT
AND FLOOR_STOCK.SITE = BOD_HEADER.SITE
WHERE BOD_HEADER.BOD_INDEX IS NULL
AND PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's')) F
ON FLOOR_STOCK.PRODUCT = F.PRODUCT
AND FLOOR_STOCK.SITE = F.SITE|||Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'INNER'.
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near 'F'.|||Ok. That is what I get for trying to code off the top of my head without QA in front of me. But try this instead:

DELETE FLOOR_STOCK
FROM FLOOR_STOCK
INNER JOIN
(SELECT FLOOR_STOCK.PRODUCT,
FLOOR_STOCK.SITE
FROM PRODUCT_MASTER
INNER JOIN FLOOR_STOCK ON PRODUCT_MASTER.PRODUCT = FLOOR_STOCK.PRODUCT
LEFT OUTER JOIN BOD_HEADER
ON FLOOR_STOCK.PRODUCT = BOD_HEADER.PRODUCT
AND FLOOR_STOCK.SITE = BOD_HEADER.SITE
WHERE BOD_HEADER.BOD_INDEX IS NULL
AND PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's')) F
ON FLOOR_STOCK.PRODUCT = F.PRODUCT
AND FLOOR_STOCK.SITE = F.SITE

According to Books Online, this transact SQL extension should work.|||That will work. If you look at the example I posted, it does basically the same thing using table aliases.|||So many answers to one simple question, I'm so confused! (Just kidding:) )
Thanks for the help, folks. I learned a few new tricks that will come in very handy.|||hey, how about some feedback?

which methods worked?

i think mine was the prettiest and simplest, but only you can tell us if it was correct|||i think mine was the prettiest and simplest, but only you can tell us if it was correct

LOL.....NICE rudy!!!!|||hey, how about some feedback?

which methods worked?

i think mine was the prettiest and simplest, but only you can tell us if it was correct

Sorry Rudy... I attempted blindman's (and derrick's) solution first, and it worked. It was as simple as I forgot to tell it which table to delete from:
I had:
Delete
from floor_stock...

And it should have been:
Delete floor_stock
from floor_stock...

However, your solution using EXISTS did help me understand how to use that clause. That's one I've always shied away from.

You Sql guys sure are territorial :D|||BOO-YAH!

Though I must concede that derrick got the solution first. :(|||I had the solution all the time, just forgot to post it...:D|||territorial? i don't think so

but hey, my solution was posted well before derrick's and blindman's :)

and mine is simpler and prettier (although i'm still waiting to hear if my assumptions about the purpose of the original LEFT OUTER JOIN were correct)|||Blah, blah, blah, blah, blah|||can't stand being one-upped, eh old meanie?

heh

;)|||huh? [:)]|||OK, what's really sad here is the fact the we are all arguing about an answer that is straight out of the DELETE syntax referenc in Books Online.

Kind of like being proud to be the first kid in the class to know what letter follows "Q" in the alphabet.|||...which, by the way, is "R". And I got it first!|||...an answer that is straight out of the DELETE syntax referenc in Books Online.with all due respect, that's bullsh1t

i checked BOL to be sure, and it does not have any example dealing with FLOOR_STOCK, PRODUCT_MASTER, or BOD_HEADER tables

the DELETE examples it does have do not feature the complex data relationship patterns of the original poster's problem

there was an inner join, a left outer join, a WHERE clause with IS NULL on a column which may or may not have been the primary key of its table, all wrapped in a derived table, inner joined to one of the original tables...

i would love to see the exact page in BOL where this answer is "straight out of"

don't just point me to the syntax, because as we all know, it is possible (and easy!!) to construct an sql statement that is syntactically valid and semantically wrong|||Except that the core problem was that he was running this:

DELETE FROM FLOOR_STOCK INNER JOIN blah blah blah...

...when he should have executed this:

DELETE FLOOR_STOCK FROM FLOOR_STOCK INNER JOIN blah blah blah...

I call that a simple syntax error...|||yes, good point -- so why didn't you say that? :)|||Now, now....In the beginning was the Word; and the word was a Book Online. Then stupid people had to screw it all up. :)

Now, let the bitch match continue. Go ahead gents!!!!|||yes, good point -- so why didn't you say that?

OK, what's really sad here is the fact the we are all arguing about an answer that is straight out of the DELETE syntax referenc in Books Online.

You are scaring me, Rudy.|||blindman does alphabet :D|||Rudy does scary stuff :D