Multi part identifier in Microsoft SQL Server

Question

I'm migrating a SQL Server 6.5 database to SQL Server 2012. Migration went well but one of the software using this database performs a strange query:

UPDATE myTable SET Field1='',Field2=0, a.Field3=14 WHERE Field10=1

The problem lies of course with the identifier ('a') before Field3. There are no views, aliases or join with that name but for SQL Server 6,5 that is a valid query.

Since I'm not familiar with SQL Server 6.5, do any of you remember the meaning of this syntax?

The software using this query is an old one of which I don't have any sources (and the company that made it is now closed...), so changing the query is not an option.

The full query is

UPDATE SituazioneMacchina 
SET DatiSetPointSpediti=''
,   SpedMappa=0
,   NrRichMappa=0
,   DatiSetPoint=''
,   a.IDProduzione=14
WHERE NumMacchina=1 

If I change "a.IDProduzione" with something like "fasdffas.IDProduzione", the query still works in SQL Server 6.5

Answer 1

As you say the query doesn't make any sense.

Probably just a parser bug in 6.5. There have been a few similar other issues ORDER BY nonsensealias.somecolumn worked until 2005 with the SQL Server 2000 behavior being

Column names in the ORDER BY clause are resolved to columns listed in the select list, regardless if they are qualified. For example, the following query executes without error:

USE pubs
SELECT au_fname AS 'FName',
  au_lname AS 'LName'
FROM authors a
ORDER BY a.LName

SQL Server ignores the qualifier a in the ORDER BY clause and resolves the column name LName to the select list.

and there is still a similar issue for INSERT statements where the following syntax is accepted.

CREATE TABLE nisse(hult int NOT NULL)
go
INSERT nisse(ab.xx.hult)
VALUES(12)
go
DROP TABLE nisse