How to write UPDATE SQL with Table alias in SQL Server 2008?

243
votes

I have a very basic UPDATE SQL -

UPDATE HOLD_TABLE Q SET Q.TITLE = 'TEST' WHERE Q.ID = 101;

This query runs fine in Oracle, Derby, MySQL - but it fails in SQL server 2008 with following error:

"Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'Q'."

If I remove all occurrences of the alias, "Q" from SQL then it works.

But I need to use the alias.

2
sqlsql-serversql-server-2008sql-updatealias
Why do you need to use an alias? It doesn't seem like you need it. – Mark Byers
Yes - from the programming perspective I do not need it. But I have an existing/old library which generates all kind of DML SQLs with table aliases. The library has lot of classes with a kind of complex logic. Now getting rid of table-aliases in the library is more work than tweaking the existing logic to work for MSSQL. Also when multiple tables are involved, I do need to have table-alias. – javauser71

2 Answers

479
votes

The syntax for using an alias in an update statement on SQL Server is as follows:

UPDATE Q
SET Q.TITLE = 'TEST'
FROM HOLD_TABLE Q
WHERE Q.ID = 101;

The alias should not be necessary here though.

19
votes

You can always take the CTE, (Common Tabular Expression), approach.

;WITH updateCTE AS
(
    SELECT ID, TITLE 
    FROM HOLD_TABLE
    WHERE ID = 101
)

UPDATE updateCTE
SET TITLE = 'TEST';