Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
 Posted - 2005-04-29 : 16:41:14
|
| SQL Server 2k SP3a.CREATE TABLE #TmpIdTable(value UNIQUEIDENTIFIER) DELETE FROM tblA WHERE MID IN (SELECT MID FROM #TmpIDTable)You'd think SQL Server would just error out but in this case ALL records in the table are deleted. Nothing is deleted if the query is fixed to read:DELETE FROM tblA WHERE MID IN (SELECT value FROM #TmpIDTable)So basically if the column name does not exist then the IN Clause is satisfying ALL records rather than none.Anyone else run into that problem?Thanks.DanielSQL Server DBA |
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-04-29 : 16:54:36
|
| It is always a good idea to qualify the table name in a subquery. i.e.:CREATE TABLE #TmpIdTable(value UNIQUEIDENTIFIER) DELETE FROM tblA WHERE MID IN (SELECT T1.MID FROM #TmpIDTable T1)This will cause an error.I have encountered other situations where, when the column names in both tables are the same, and you would expect that the comparison would be done on the column referenced in the table in the subselect. that the outer tables' column is being comared to itself. Obviously not quite right, but maybe not a bug. Your example shows that happening.BTW: I recommend using "Exists" instead of "In" whenever possible. |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-04-29 : 17:26:32
|
quote:
Originally posted by KLang23
It is always a good idea to qualify the table name in a subquery. i.e.:CREATE TABLE #TmpIdTable(value UNIQUEIDENTIFIER) DELETE FROM tblA WHERE MID IN (SELECT T1.MID FROM #TmpIDTable T1)This will cause an error.I have encountered other situations where, when the column names in both tables are the same, and you would expect that the comparison would be done on the column referenced in the table in the subselect. that the outer tables' column is being comared to itself. Obviously not quite right, but maybe not a bug. Your example shows that happening.BTW: I recommend using "Exists" instead of "In" whenever possible.
Yup, That makes sense. It's that correlated subquery stuff...  A developer wrote this one and got mixed up and included the wrong column name. I was able to trace the table truncation down to this block of code that he wrote and fixed the SP but didnt know why the IN Clause satisfied the query.Thanks,DanielSQL Server DBA |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-04-29 : 17:48:39
|
| Rule of thumb: in ANY SQL statement involving more than 1 table, fully qualify every column ...- Jeff |
|
|
|
dursaliye
Starting Member
22 Posts |
Posted - 2005-04-30 : 08:27:53
|
| Its reason is that the Query Optimizer converts the IN clause to the OR operator when parsing your code. |
|
|
|
|
|
|
SQL Server bug with IN Clause?