Reference to non-existent column does not error

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  July 13, 2016 at 4:08 pm

  #400401

  I'm in the process of re-writing some inefficient, looping code from a former developer and I've stumbled across something that has me quite stumped. I've greatly simplified the original code into an example, but I can reproduce the behavior in the example.

  So the original code loads up some table variables, then loops through them and uses the data to update a target table. I don't need help re-writing the code from a loop to set-based (it's already done), I just want some feedback as to why SQL Server would even allow this code to run w/o throwing an error.

  Again, this is a greatly simplified mock up of the original code. It does not loop, but I've left the syntax statement of the UPDATE statement just like it was in the original WHILE loop to demonstrate the anomaly.

  DECLARE @Target TABLE (ID int, Name varchar(20), DateColumn datetime)

  INSERT INTO @Target

  VALUES (1, 'Test1', GETDATE())

  , (2, 'Test2', GETDATE())

  SELECT * FROM @Target

  DECLARE @Source TABLE (ID int, Name varchar(20))

  INSERT INTO @Source

  VALUES (1, 'NewTest1')

  SELECT * FROM @Target

  --SELECT DateColumn FROM @Source WHERE ID = 1

  UPDATE @Target

  SETName = (SELECT Name FROM @Source WHERE ID = 1)

  , DateColumn = (SELECT DateColumn FROM @Source WHERE ID = 1)

  WHEREID = 1

  SELECT * FROM @Target

  As you see, the UPDATE statement is referencing a column in the Source table that does not exist and the UPDATE does not error. It does not update that column, but the Name column does get updated, telling me that SQL Server is totally OK with this statement. If you uncomment the commented out SELECT statement, you'll see that a reference to the non-existent column outside of the UPDATE throws an error as expected.

  Has anyone else ever seen this?

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  July 13, 2016 at 5:01 pm

  #1889990

  It's a common misconception that the columns SELECTED in a subquery have to come from the tables in the subquery.

  If you use table aliases and two part naming on all columns, you will get the error you expect.

  Without that, Datecolumn in the subquery is just the Datecolumn from @Target

  This will error:

  UPDATE @Target

  SETName = (SELECT Source.Name FROM @Source AS Source WHERE Source.ID = 1)

  , DateColumn = (SELECT Source.DateColumn FROM @Source AS Source WHERE Source.ID = 1)

  WHEREID = 1

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]