Larry.Findley (6/24/2013)
In the following NON-CORRELATED subqueryDELETE
FROM dbo.DimCustomer_LLF
WHERE CustomerKey in (SELECT DISTINCT(CustomerKey) FROM dbo.cdc_states);
GO
we should get a syntax error. CustomerKey is not a column in dbo.cdc_states. If we apply the rules for correlated subqueries and qualify everything then the systax error occurs as expected. The desired result was to delete a subset of the rows in dbo.DimCustomer_LLF. What happens instead is that all rows are deleted.
What are we missing? Is this documented somewhere (we haven't found it in any searches)?
This seems like a serious error in the DBMS.
This is not an error with SSMS. Since CustomerKey is in the DimCustomer table it is within scope of the subquery. The query is a bit odd but syntactically there is nothing wrong with it. Your subquery is now selecting all distinct CustomerKey values from cdc_states.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/