How to write UPDATE SQL with Table alias in SQL Server 2008?
SqlSql ServerSql Server-2008Sql UpdateAliasSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
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';