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

• Microsoft SQL Server version 7.0

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 

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

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 

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.

Additional query words:

Keywords : kbSQLServ700 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug