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

delete trigger for multiple row deletes

I'm racking my brain trying to figure this one out and I have a feeling it's
simpler than I'm making it out to be. I need a trigger that will prevent
delete of certain rows but not all. For example, I have a customer table:
CustID INT (primary key, auto inc)
CustName VARCHAR(50)
and an orders table:
OrderID INT (primary key, auto inc)
CustID INT (FK to other table of course)
ItemOrdered VARCHAR(100)
Here would be the data:
Customer table:
CustID CustName
1 Jones Lumber
2 Smith's Cars
3 Helen's Carpet
Orders table:
OrderID CustID ItemOrdered
1 1 2x4's
2 1 Dowel
3 3 Berber
4 3 Shag
Notice that Smith's Cars is not in the orders table at all.
Now I select all the rows in the customer table and try to delete them. I'd
like the trigger to prevent the delete of any customer that has orders but
allow the others to be deleted. So in this case Jones Lumber and Helen's
Carpet would remain and Smith's Cars would be deleted.
The trouble I'm having is that if I set up a cursor in the trigger to
sequence through the customers, how do I rollback just some of the deletes
but not all and put up an appropriate message?
DECLARE
@.iCustID AS INT,
@.sMsg AS VARCHAR(500)
SET NOCOUNT ON
SET @.sMsg = ''
DECLARE curCustomers CURSOR FOR SELECT C.CustID FROM DELETED C
OPEN curCustomers
FETCH NEXT FROM curCustomers INTO @.iCustID
IF @.@.FETCH_STATUS = 0
BEGIN
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(*) FROM tblOrders WHERE CustID = @.iCustID) > 0
BEGIN
ROLLBACK TRANSACTION
SET @.sMsg = 'Customers with orders cannot be deleted.'
END
FETCH NEXT FROM curCustomers INTO @.iCustID
END
END
IF @.sMsg <> ''
RAISERROR (@.sMsg, 16, 1)
CLOSE curCustomers
DEALLOCATE curCustomers
===
My original trigger looked like this until I realized it was rolling back
the whole thing without even attempting to test for rows that should be
allowed to be deleted:
IF (SELECT COUNT(*) FROM tblOrders WHERE CustID IN (SELECT CustID FROM
DELETED)) > 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Customers with orders cannot be deleted.', 16, 1)
END
Thanks,
KeithKeith G Hicks wrote:
> I'm racking my brain trying to figure this one out and I have a feeling it
's
> simpler than I'm making it out to be. I need a trigger that will prevent
> delete of certain rows but not all. For example, I have a customer table:
> CustID INT (primary key, auto inc)
> CustName VARCHAR(50)
> and an orders table:
> OrderID INT (primary key, auto inc)
> CustID INT (FK to other table of course)
> ItemOrdered VARCHAR(100)
> Here would be the data:
> Customer table:
> CustID CustName
> 1 Jones Lumber
> 2 Smith's Cars
> 3 Helen's Carpet
> Orders table:
> OrderID CustID ItemOrdered
> 1 1 2x4's
> 2 1 Dowel
> 3 3 Berber
> 4 3 Shag
> Notice that Smith's Cars is not in the orders table at all.
> Now I select all the rows in the customer table and try to delete them. I'
d
> like the trigger to prevent the delete of any customer that has orders but
> allow the others to be deleted. So in this case Jones Lumber and Helen's
> Carpet would remain and Smith's Cars would be deleted.
> The trouble I'm having is that if I set up a cursor in the trigger to
> sequence through the customers, how do I rollback just some of the deletes
> but not all and put up an appropriate message?
> DECLARE
> @.iCustID AS INT,
> @.sMsg AS VARCHAR(500)
> SET NOCOUNT ON
> SET @.sMsg = ''
> DECLARE curCustomers CURSOR FOR SELECT C.CustID FROM DELETED C
> OPEN curCustomers
> FETCH NEXT FROM curCustomers INTO @.iCustID
> IF @.@.FETCH_STATUS = 0
> BEGIN
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(*) FROM tblOrders WHERE CustID = @.iCustID) > 0
> BEGIN
> ROLLBACK TRANSACTION
> SET @.sMsg = 'Customers with orders cannot be deleted.'
> END
> FETCH NEXT FROM curCustomers INTO @.iCustID
> END
> END
> IF @.sMsg <> ''
> RAISERROR (@.sMsg, 16, 1)
> CLOSE curCustomers
> DEALLOCATE curCustomers
>
> ===
> My original trigger looked like this until I realized it was rolling back
> the whole thing without even attempting to test for rows that should be
> allowed to be deleted:
> IF (SELECT COUNT(*) FROM tblOrders WHERE CustID IN (SELECT CustID FROM
> DELETED)) > 0
> BEGIN
> ROLLBACK TRANSACTION
> RAISERROR ('Customers with orders cannot be deleted.', 16, 1)
> END
> Thanks,
> Keith
Do not use cursors in triggers (or anywhere else you don't have to for
that matter).
I've included a solution below but I don't really see the point. Why
not just modify your original DELETE statement to exclude the
referenced rows using the NOT EXISTS check? The foreign key keeps you
safe from accidental deletes.
CREATE TRIGGER trg_customer
ON customer INSTEAD OF DELETE
AS
IF EXISTS
(SELECT *
FROM deleted
WHERE EXISTS
(SELECT *
FROM orders
WHERE custid = deleted.custid))
RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
DELETE FROM customer
WHERE EXISTS
(SELECT *
FROM deleted
WHERE custid = customer.custid)
AND NOT EXISTS
(SELECT *
FROM orders
WHERE custid = customer.custid);
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Ok so just so I understand your strategy, a rollback is not necessary since
you doing INSETAD OF and are putting up a warning if there are any rows that
cannot be deleted and then actually performing the delete on the allowed
rows within the trigger?
The foreign key won't help me here. The actual project is slightly more
complex but my sample got the point across and your solution should do the
trick nicely. Very much appreciated.
What's the reason for avoiding cursors? Is it a performance issue or are
there other reasons?
Thanks again,
Keith
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1140822038.662268.199890@.e56g2000cwe.googlegroups.com...
Do not use cursors in triggers (or anywhere else you don't have to for
that matter).
I've included a solution below but I don't really see the point. Why
not just modify your original DELETE statement to exclude the
referenced rows using the NOT EXISTS check? The foreign key keeps you
safe from accidental deletes.
CREATE TRIGGER trg_customer
ON customer INSTEAD OF DELETE
AS
IF EXISTS
(SELECT *
FROM deleted
WHERE EXISTS
(SELECT *
FROM orders
WHERE custid = deleted.custid))
RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
DELETE FROM customer
WHERE EXISTS
(SELECT *
FROM deleted
WHERE custid = customer.custid)
AND NOT EXISTS
(SELECT *
FROM orders
WHERE custid = customer.custid);
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I implemented your suggestion and it works fine if I swap the sections so
that the test for Raiserror is happening after the delete is actually done.
Otherwise nothing deletes for me if it's done in the order you have below.
Can you explain why?
Thanks,
Keith
CREATE TRIGGER trg_customer
ON customer INSTEAD OF DELETE
AS
DELETE FROM customer
WHERE EXISTS
(SELECT *
FROM deleted
WHERE custid = customer.custid)
AND NOT EXISTS
(SELECT *
FROM orders
WHERE custid = customer.custid);
IF EXISTS
(SELECT *
FROM deleted
WHERE EXISTS
(SELECT *
FROM orders
WHERE custid = deleted.custid))
RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Never mind on this question. It works fine either way.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:e6xNt2ZOGHA.2604@.TK2MSFTNGP09.phx.gbl...
I implemented your suggestion and it works fine if I swap the sections so
that the test for Raiserror is happening after the delete is actually done.
Otherwise nothing deletes for me if it's done in the order you have below.
Can you explain why?
Thanks,
Keith|||There are several reasons to avoid cursors. Set-based operations generally
perform better: heap and index maintenance can be optimized, and transaction
logging is minimized. In addition, triggers fire only once for a set-based
operation, whereas they fire once per iteration for a row-based operation.
But these aren't the only reasons. Cursors reduce the effective isolation
level to REPEATABLE READ if a transaction is outstanding or to READ
COMMITTED if one isn't. Also, if a transaction is outstanding and
modifications occur within the fetch loop, the probability of deadlocks
increases significantly. You should know that a transaction is always
outstanding within the body of a trigger. While there are rare occasions
where a cursor will improve performance, all of the above factors must be
taken into account before converting a set-based solution into a row-based
one. An important note: you should always attempt a set-based solution
first, and only after every other option has failed, such as adding indexes,
hints, and ultimately SET FORCEPLAN, should you convert a set-based solution
to a row-based one. As you can see, cursors are not for the uninitiated.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23uVtziZOGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Ok so just so I understand your strategy, a rollback is not necessary
> since
> you doing INSETAD OF and are putting up a warning if there are any rows
> that
> cannot be deleted and then actually performing the delete on the allowed
> rows within the trigger?
> The foreign key won't help me here. The actual project is slightly more
> complex but my sample got the point across and your solution should do the
> trick nicely. Very much appreciated.
> What's the reason for avoiding cursors? Is it a performance issue or are
> there other reasons?
> Thanks again,
> Keith
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1140822038.662268.199890@.e56g2000cwe.googlegroups.com...
>
> Do not use cursors in triggers (or anywhere else you don't have to for
> that matter).
> I've included a solution below but I don't really see the point. Why
> not just modify your original DELETE statement to exclude the
> referenced rows using the NOT EXISTS check? The foreign key keeps you
> safe from accidental deletes.
> CREATE TRIGGER trg_customer
> ON customer INSTEAD OF DELETE
> AS
> IF EXISTS
> (SELECT *
> FROM deleted
> WHERE EXISTS
> (SELECT *
> FROM orders
> WHERE custid = deleted.custid))
> RAISERROR ('Customers with orders cannot be deleted.', 16, 1);
> DELETE FROM customer
> WHERE EXISTS
> (SELECT *
> FROM deleted
> WHERE custid = customer.custid)
> AND NOT EXISTS
> (SELECT *
> FROM orders
> WHERE custid = customer.custid);
> GO
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Thanks for all the info Brian.

Delete SQL Query - Brain Hurty

It's been a long day and I can't get this one right! Ugh!!! Should be
an EASY one...
Two tables:
Sales (IdProduct)
StoreListings (IdProduct)
I am trying to remove all items from our StoreListings database table
that have not had more than 15 sales, that is, they do not have more
than 15 records in the Sales table. I need a query that deletes from
StoreListings if the record count in Sales is < 15.
This is causing me great pain...delete StoreListing
where exists (select 1
from (select storeListingKey
from sales
group by storeListingKey
having count(*) < 15) as badSales
where storeListing.storeListingkey =
badSales.storeListingkey)
If this isn't right, post a script of your tables, just basic keys and
pertinent columns are all that is needed. Then a bit of data (we can change
it to 2 or 3 sales for developing the query.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<superfreaker@.gmail.com> wrote in message
news:1138151660.815868.236040@.g49g2000cwa.googlegroups.com...
> It's been a long day and I can't get this one right! Ugh!!! Should be
> an EASY one...
> Two tables:
> Sales (IdProduct)
> StoreListings (IdProduct)
> I am trying to remove all items from our StoreListings database table
> that have not had more than 15 sales, that is, they do not have more
> than 15 records in the Sales table. I need a query that deletes from
> StoreListings if the record count in Sales is < 15.
> This is causing me great pain...
>|||DELETE FROM StoreListing x
WHERE (SELECT COUNT(*)
FROM sales y
WHERE y.storeListingkey=x.storeListingkey)<15
"superfreaker@.gmail.com" wrote:

> It's been a long day and I can't get this one right! Ugh!!! Should be
> an EASY one...
> Two tables:
> Sales (IdProduct)
> StoreListings (IdProduct)
> I am trying to remove all items from our StoreListings database table
> that have not had more than 15 sales, that is, they do not have more
> than 15 records in the Sales table. I need a query that deletes from
> StoreListings if the record count in Sales is < 15.
> This is causing me great pain...
>|||DELETE FROM StoreListings
WHERE [IdProduct] IN
(SELECT t1.[IdProduct] FROM
(SELECT [IdProduct], COUNT(*) FROM Sales
GROUP BY [IdProduct]
HAVING COUNT(*) < 15
) t1
)
"superfreaker@.gmail.com" wrote:

> It's been a long day and I can't get this one right! Ugh!!! Should be
> an EASY one...
> Two tables:
> Sales (IdProduct)
> StoreListings (IdProduct)
> I am trying to remove all items from our StoreListings database table
> that have not had more than 15 sales, that is, they do not have more
> than 15 records in the Sales table. I need a query that deletes from
> StoreListings if the record count in Sales is < 15.
> This is causing me great pain...
>