Here’s a funny thing I discovered while working with a table I created by importing data from Excel. (I know, I only have myself to blame.) A number of the column names ended up with a trailing space on the end. They were Y/N type columns where the value was either Y or null. I decided to update all the nulls to Ns so the nulls wouldn’t trip me up later. So I typed a query like this: update
set = isnull(, ‘N’). I noticed Intellisense had underlined the column name but I knew what I was doing and assumed it was still refreshing its cache. The update ran fine but when I tried to select that column I got a ‘no such column’ error. WTF?
I can only assume this is a side-effect of the way you can update things like rowset functions and not just tables. I’ve done a bit of playing around and found that (surprise surprise) some variants will work and some will fail. Try it for yourself:
create table dbo.TestTable
[TestColumn ] varchar(20) null
--This update will give the result: (0 row(s) affected)
set TestColumn = 'hello';
--But this update will fail:
--Msg 207, Level 16, State 1, Line 2
--Invalid column name 'TestColumn2'.
set TestColumn2 = 'hello';
drop table dbo.TestTable;
I suppose the moral of the story is: concentrate and check that updates affect the number of rows you expect. (Damn, that’s not going to work!)