BUG: ERR 107 Occurs when Alias Name of Derived Table not Found in UPDATE/DELETE Statement (225492)

SYMPTOMS

If a FROM clause of a query contains a join with a derived table and the derived table is the last table of the FROM clause, the UPDATE and DELETE statements fail in the parsing state with error 107 as follows:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 't' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 2, Line 2
The column prefix 't' does not match with a table name or alias name used in the query.

WORKAROUND

Reverse the table order to reference the derived table first as follows:

begin tran
go
update authors
set phone = '415-123-4567'
from (select * from titleauthor where au_ord = 1) as t
join authors as a
 on (a.au_id = t.au_id)
go
rollback tran
go

MORE INFORMATION

For example, the following queries fail:

begin tran
go
update authors
set phone = '415-123-4567'
from authors as a
join (select * from titleauthor where au_ord = 1) as t
 on (a.au_id = t.au_id)
go

delete authors
from authors as a
join (select * from titleauthor where au_ord = 1) as t
 on (a.au_id = t.au_id)
go
rollback tran
go