Ambiguous column name on update of new column used in view

Scenario

Adding a column to a table and then updating that column

alter sometable add example_column_name varchar(255);
update sometable set example_column_name = '';

(real update is a bit more complex, but this is a boiled down version we used trying to find the problem)

Problem

The update query gives ‘Ambiguous column name example_column_name.’

• This works in all databases except one.
• It is only for exactly one specific column name it happens, adding a column with different name and updating that column works
• The column name in question works in other databases, and it already exists in other tables in the same db

Question

Does anyone know what’s going on, how can we get past this problem?

Update

The problem was an indexed view that used the column name of the new column in an already existing query. See comments and accepted answer for details.

Answer

This error can’t happen purely from the code shown.

There must be a trigger or indexed view in play. You have ruled out triggers so an example demonstrating the indexed view scenario is below

CREATE TABLE T1(X INT, Y INT)
CREATE TABLE T2(X INT, Z INT)

GO

CREATE VIEW V1
WITH SCHEMABINDING
AS
  SELECT T1.X,
         T1.Y,
         Z
  FROM   dbo.T1
         JOIN dbo.T2
           ON T1.X = T2.X

GO

CREATE UNIQUE CLUSTERED INDEX IX
  ON V1(X)

GO

ALTER TABLE T1
  ADD Z INT;

GO

UPDATE T1
SET    Z = 0 

When the view is initially created the only table containing a column Z is T2 so it is not ambiguous. After adding column Z to T1 the view definition becomes ambiguous. The UPDATE to the table tries to automatically maintain the view and the error is thrown.

Msg 209, Level 16, State 1, Procedure V1, Line 5 [Batch Start Line 23]
Ambiguous column name ‘Z’. Msg 4413, Level 16, State 1, Line 25 Could
not use view or function ‘V1’ because of binding errors.

It is best practice to always use two part naming where your query references more than one table to avoid this type of error.