August 17, 2010 at 10:58 am
create a table in database 1 with two columns
column1 - customerid (guid)
column2 - emailaddress (varchar 100)
add 10 records in this table (values don't matter)
run
select top 5
customerid, emailaddress as email
into database2.dbo.table2
from database1.dbo.table1
now run
delete from database1.dbo.table1
where emailaddress in
(
select emailaddress from database2.dbo.table2
)
the desired result would be that sql server would throw an error like "Invalid column name 'emailaddress'." for table2
The actual result is; it deletes all the records from table1 and throws no error.
I described the scenario above to closely reflect something I have seen happen multiple times.
Is this a configuration issue?
Is this due to multiple databases being involved?
I have run this using mgmt studio from database1, database2 and master with same results.
any help would be appreciated.
August 17, 2010 at 11:14 am
You have run in to what may seem odd, but is correct (and I believe documented) behaviour.
The problem can be seen in any subquery when the inner query refers to what you INTENDED to be a column in the table referred to in the inner query, but that column does not exist. It will take the value for the incorrectly named column from the outer query.
Moral of this is always use correlation names (aliases) thus..
delete from tableA
where tableA.colx in (select B.coly from dbo.tableB as B)
Mike
August 17, 2010 at 3:10 pm
Mike John (8/17/2010)
You have run in to what may seem odd, but is correct (and I believe documented) behaviour.
Yup. That it is.
Columns in the outer query are visible inside subqueries. If they weren't, you wouldn't be able to correlate a subquery. Since the column in question is a valid name of a column in scope it is not an error. It's almost certainly not what you intended to happen, but it is correct behaviour from SQL.
This is why one should prefix column names with their table names.
delete from database1.dbo.table1 t1
where t1.emailaddress in
(
select t2.emailaddress from database2.dbo.table2 t2
)
That will throw the expected error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply