Search code examples
sqlsql-serversql-server-2008sql-updatealias

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

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.

Solution

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