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

sql sql-server sql-server-2008 sql-update alias

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.

Why do you need to use an alias? It doesn't seem like you need it.

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.

Mark Byers

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.

Yes !!! It works. Thanks for the quick response. By any chance do you know why MSSQL server supports such unconventional syntax for update?

Mark Byers - Great Answer!! This syntax allows me to add a commented out Select statement, which allows me to test the update by doing the select first (highlight from the select down and execute): SET Q.TITLE = 'TEST' -- SELECT *

Nice. This makes it easier to use intellisense in the where clause.

That's not an alias. That's just the fully qualified 'table.column' name :-/

@ScottWelker - HOLD_TABLE is the table. Q is the alias. TITLE is the column.

Ryk

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

Yes - it also works. But for a JDBC/Java program this is a kind of complex syntax. Thanks for your response.

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

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Links

Contact US