Microsoft KB Archive/225492

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

Article ID: 225492

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q225492



BUG #: 55070 (SQLBUG_70)



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 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

With this Error:

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.

Keywords: kbbug kbpending KB225492

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.