My frequent SQL forum

My frequent sql forum

Strange behavior of MS SQL

Strange behavior of MS SQL
Let me show that with an example.

set nocount on
--- create table----
CREATE TABLE #partMaster (
PART_CODE VARCHAR(18),
PART_TYPE CHAR(1),
PART_STATUS CHAR(1),
)
go
CREATE TABLE #master(
PRODUCT VARCHAR(18)
)

go
---insert statement
INSERT INTO #partMaster (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 #master(PRODUCT)
SELECT 'ABC123' UNION ALL
SELECT 'DEF123' UNION ALL
SELECT 'ABC456' UNION ALL
SELECT 'DEF456'

go
--- select statement before delete
select * from #partMaster
select * from #master
go
--delete statement
DELETE FROM #master
WHERE PRODUCT IN ( SELECT PRODUCT
FROM #partMaster
WHERE PART_STATUS IN ('I', 'D')
AND PART_TYPE NOT IN ('F', 'N', 'K', 'D')
)
GO
--- select statement after delete
select * from #partMaster
select * from #master
go
--drop table
drop table #master
drop table #partMaster


oops! all the records in #master is gone.
u can see there is no 'PRODUCT' column in #partMaster,which is in the delete statement.We expect sql will throw error.No!,it will delete all the records in #master.
whats happened is sql server run time engine would look 'PRODUCT' column first in #partMaster,it would look in #master table if it is not found in #partMaster.All condition matches here in where clause 'PRODUCT=PRODUCT' all records will delete.

Now u think this case wont happened.yes it can,Imagine u had column 'PRODUCT' in #partMaster and dropped later but missed to change ur scripts.

Beware!