A case of "OverDeletion"

I added this line to a sproc yesterday:

--get rid of the product master for inactive raw-mats/packaging
DELETE FROM PRODUCT_MASTER
WHERE PRODUCT IN
(SELECT PRODUCT FROM SAPROD_PLAN.DBO.PARTMSTR
WHERE PART_STATUS IN ('I','D') AND PART_TYPE NOT IN ('F','N','K','D'))

This sql statement should have thrown an error, because there is no column named "product" in the table partmstr. Instead, all of the data in the table
product_master was deleted. Of course, this table cascade deletes to 18 other tables, so I lost a whole slew of data. No big deal there, I can get a tape backup. Just curious how it could have even happened in the first place.I added this line to a sproc yesterday:

--get rid of the product master for inactive raw-mats/packaging
DELETE FROM PRODUCT_MASTER
WHERE PRODUCT IN
(SELECT PRODUCT FROM SAPROD_PLAN.DBO.PARTMSTR
WHERE PART_STATUS IN ('I','D') AND PART_TYPE NOT IN ('F','N','K','D'))


This sql statement should have thrown an error, because there is no column named "product" in the table partmstr. Instead, all of the data in the table
product_master was deleted. Of course, this table cascade deletes to 18 other tables, so I lost a whole slew of data. No big deal there, I can get a tape backup. Just curious how it could have even happened in the first place.

u are kidding.Delete statement will raise error if any column name u mentioned in sql statement which is not in ur table.
Im 100% sure deletion will not happened in such case.|||U funny

You should post the DDL for both tables and provide some sample data.

Look at the sticky's at the top of the page, but...

USE Northwind
GO

SET NOCOUNT ON
SELECT * INTO myOrders99 FROM Orders
GO

sp_help myOrders99
GO

DELETE FROM myOrders99 WHERE Brett IN (SELECT Brett FROM myOrders99)
GO

SET NOCOUNT OFF
DROP TABLE myOrders99
GO|||Do you have a couple of PARTMSTR tables? I see this one is declared with the database name, as well as owner.|||This duplicates the problem...

CREATE PROCEDURE TMPTEST
AS
CREATE TABLE TMPPARTMSTR (
PART_CODE VARCHAR(18),
PART_TYPE CHAR(1),
PART_STATUS CHAR(1),
)

CREATE TABLE TMPPRODUCT_MASTER(
PRODUCT VARCHAR(18)
)

INSERT INTO TMPPARTMSTR (PART_CODE, PART_TYPE, PART_STATUS)
SELECT 'ABC123', 'F', 'A' UNION ALL
SELECT 'DEF123','K','A' UNION ALL
SELECT 'ABC456','R','A' UNION ALL
SELECT 'DEF456','R','I'

INSERT INTO TMPPRODUCT_MASTER(PRODUCT)
SELECT 'ABC123' UNION ALL
SELECT 'DEF123' UNION ALL
SELECT 'ABC456' UNION ALL
SELECT 'DEF456'

DELETE FROM TMPPRODUCT_MASTER
WHERE (PRODUCT IN
(SELECT PRODUCT
FROM TMPPARTMSTR
WHERE PART_STATUS IN ('I', 'D') AND PART_TYPE NOT IN ('F', 'N', 'K', 'D')))
GO

It ends up deleting all 4 rows from tmpproduct_master, when it should throw an error.|||It looks like it's coorelating the column without a lable because it's unique

SELECT * FROM TMPPRODUCT_MASTER
WHERE PRODUCT IN ( SELECT PRODUCT
FROM TMPPARTMSTR
WHERE PART_STATUS IN ('I', 'D')
AND PART_TYPE NOT IN ('F', 'N', 'K', 'D'))|||Hi,
Initially i found this query strange and thought that ur statement is not valid,but after testing it manually i found that the statement given is absolutely correct,and it even worked in the same way in oracle also..

The only way one can restrict this is by adding the table name in front of the column name
eg:
delete from deal where dealid in (select tst1.dealid from tst1 where id1=1)
where dealid is a column of deal table and id1 is the col of tst1 table..

After checking the execution plan I can see that
1.DB checks for the availability of clustered index for deal.dealid with the condition deal.dealid=deal.dealid(ordered)
2.Scans the inner query for tst1.id1=1.

one more weird thing which i have observed is
delete from deal where dealid in (select tst1.dealid from tst1 where dealid=1)
also works fine and deletes the rows from the deal table..

May be this is a bug in SQL.

regards,
Pavan.|||This duplicates the problem...

DELETE FROM TMPPRODUCT_MASTER
WHERE (PRODUCT IN
(SELECT PRODUCT
FROM TMPPARTMSTR
WHERE PART_STATUS IN ('I', 'D') AND PART_TYPE NOT IN ('F', 'N', 'K', 'D')))
GO


It ends up deleting all 4 rows from tmpproduct_master, when it should throw an error.

PRODUCT column is not in table TMPPARTMSTR and it is refered from TMPPRODUCT_MASTER .Obviously condition would be true and deleted all records.

In future always add alias name with table and refer ur column with alias name.|||It's not a bug.

But, why does this work

DELETE FROM TMPPRODUCT_MASTER
WHERE PRODUCT IN ( SELECT PART_CODE
FROM TMPPARTMSTR
WHERE PART_STATUS IN ('I', 'D')
AND PART_TYPE NOT IN ('F', 'N', 'K', 'D'))

SELECT * FROM TMPPRODUCT_MASTER

But this does not? (Not firing all the neurons today I guess)

DELETE FROM TMPPRODUCT_MASTER o
WHERE EXISTS ( SELECT *
FROM TMPPARTMSTR i
WHERE PART_STATUS IN ('I', 'D')
AND PART_TYPE NOT IN ('F', 'N', 'K', 'D')
AND i.PART_CODE = o.PRODUCT)|||PRODUCT column is not in table TMPPARTMSTR and it is refered from TMPPRODUCT_MASTER .Obviously condition would be true and deleted all records.

So, are you saying: because the subquery can't find a column named "product" in the only table in the subquery, it will go OUTSIDE of the subquery to look in other tables contained in the query? That seems counterintuitive to me, but that is what it appears to be doing.|||That's a fact JACK

Look at my SELECT Statement as a sample...

I thought it only coorelated in the predicate and not in the SELECT|||Well, between what I've read here and in BOL, I almost understand whats happening. In the future, I'll stick with the advice given here, and use tablename.columnname, even though BOL doesn't do that in some of their examples.

Learn something new every time I come here.
Thanks!|||tablename.columnname, even though BOL doesn't do that in some of their examples.

ahhh the blind dude way...

I use aliases instead

SELECT * FROM Orders o JOIN [Order Details] d ON o.OrderId = d.OrderId

Mr. dude fully qualifies everything, because, well, he's blind you see|||The blind dude codes with style!sql