1

I wanted to warn people about a problem I hit (luckily in a test environment and not LIVE) when using the SQL IN statement as part of the WHERE clause.

I built a temporary table called [dataimport].[IDsToUpdate], which contained a list of 42 GUIDs that I wanted to update using the following UPDATE statement:

UPDATE dbo.IndividualSponsors
SET LeaveDate = null
WHERE IndividualSponsorID in (select IndividualSponsorID from [dataimport].[IDsToUpdate])

The [dataimport].[IDsToUpdate] table contains a single GUID column called IndvSponsorID. Note the error in my UPDATE statement. It should have been:

select IndvSponsorID from [dataimport].IDsToUpdate

If I run select IndividualSponsorID from [dataimport].IDsToUpdate on its own, it errors saying that the column IndividualSponsorID is unknown (as expected).

But when I run it inside the UPDATE statement above, it updated all 148,000 records in my dbo.IndividualSponsors table (arrrrhhh !!!).

I believe this is because the column name IndividualSponsorID exists in the table dbo.IndividualSponsors, and when run in the full context of the UPDATE statement, SQL Server is re-pointing the column reference to that table hence the blanket update of all rows.

This seems VERY wrong to me, especially as I explicitly stated that the column IndividualSponsorID is FROM the table [dataimport].IDsToUpdate. It should have error'ed, as my IN SELECT statement was incorrect.

Morale: ALWAYS use table alias names when doing UPDATES (especially to live data!):

e.g. select ids.IndividualSponsorID from [dataimport].IDsToUpdate ids

This fails as expected.

Does anyone have a different view on this?

Fred
  • 5,663
  • 4
  • 45
  • 74
Nick Wright
  • 1,403
  • 13
  • 19
  • 6
    This is working as specified by the SQL standard. –  Oct 25 '13 at 10:22
  • This is standard behaviour. – gregpakes Oct 25 '13 at 10:22
  • possible duplicate of [sql server 2008 management studio not checking the syntax of my query](http://stackoverflow.com/questions/4594733/sql-server-2008-management-studio-not-checking-the-syntax-of-my-query) – Martin Smith Oct 25 '13 at 10:24
  • 2
    I don't think this should counted a error. The column IndividualSponsorID is a valid column in the full statement. You can also simple us "select IndividualSponsorID" without any table names. what happens with you subselbst from that import table is, you get a rows of the same id with a count of rows in import table. is also right, because you can specify static column values like "select 1,2,3". finally it compares it and of curse that where clause matches the same value on both sides of that clause. effect: all rows are affected. – coding Bott Oct 25 '13 at 10:28

1 Answers1

2

I agree with the commenters. It's unfortunate that you had a typo that led to a bad update but SQL is doing the right thing. Imagine it didn't let you use IndividualSponsorID and you wanted to write an inner select like this:

(select IndvSponsorID + IndividualSponsorID from [dataimport].[IDsToUpdate])

One column comes from the table referenced in the inner select and the other from the table in the outer update. There are perfectly good scenarios where you might need something like that.

The real lesson here is to run your manual updates in a transaction so that you can easily roll it back if you notice that the number of rows affected doesn't match what you expected.

SandPiper
  • 2,816
  • 5
  • 30
  • 52
acfrancis
  • 3,569
  • 25
  • 21
  • There are perfectly good scenarios where you might need something like that. [But arguably the language could insist that you explicitly prefix the table name when using columns from the outer scope](http://www.sommarskog.se/strict_checks.html#columnprefixes) – Martin Smith Oct 25 '13 at 10:35
  • Nice idea but that would make it slightly more cumbersome to write simple queries. Personally, I like having the ability to write "quick and dirty" scripts when I need them but I try to be responsible about it. – acfrancis Oct 25 '13 at 10:40
  • 1
    It is a very common "gotcha" though. I regularly see this reported as a bug on the Microsoft Connect site. I suppose it can also occur in theory to a query that once worked correctly if a column is dropped from the inner table. – Martin Smith Oct 25 '13 at 10:41