SQL Server syntax error identifier could not be bound

Question

Why is this SQL getting a syntax error and how can it be fixed?

UPDATE s
SET s.modified_date = l.action_date
FROM
(SELECT l.action_date, l.user_id FROM item_audit_log) l
WHERE l.user_id = s.staff_id

Error:

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "l.action_date" could not be bound.

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "l.user_id" could not be bound.

Gordon Linoff Gordon Linoff · Accepted Answer · 2015-09-17T01:56:34

You are getting the error because your subquery references "l" in the subquery, but it is not defined inside the subquery (it is defined in the outer scope). However, you don't need the subquery:

UPDATE s
    SET modified_date = l.action_date
    FROM staffs s JOIN
         item_audit_log l
         ON l.user_id = s.staff_id;

This assumes you have a table called s. I imagine that this really should be an alias, also defined in the FROM clause.

SQL Server syntax error identifier could not be bound

2 Answers