SProc won't compile in one DB but is fine in another

[tombos]

In SQL 7 with the latest service packs, I have a stored procedure (actually several) that uses the following type of statement:

UPDATE Table1
SET a.col_abc=b.col_def, a.col_stu=b.col_xyz
FROM Table1 a, Table2 b
WHERE a.col_pqr=b.col_pqr

Of course, all those column names are arbitrary. Bottom line: I'm copying values from Table2 to Table1 based on a key value that exists in both.

I have two databases with identical table structures, one that was originally created in SQL 6.5 and one that was just created in SQL 7 by scripting all the tables in the first database (including keys and indexes).

This UPDATE command structure works fine in the original database; however, whenever I use this format in the newer database, I get the following error message:

"Cannot use the column prefix 'a'. This must match the object in the UPDATE clause 'Table1'."

If I get rid of the 'a' alias and reference my columns in the manner Table1.colname, then it compiles fine.

Why would I encounter this difference? I like the shorthand method much better!

 

[Jocsta]

I think I remember coming across something in BOL last week, that says that post v6.5 you have to use the alias name for the update table if an alias is used in the from clause, unless you change the compatibility level to 65.
So your query needs to become

UPDATE a
SET a.col_abc=b.col_def, a.col_stu=b.col_xyz
FROM Table1 a, Table2 b
WHERE a.col_pqr=b.col_pqr

<< JOC >>