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.
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.
HOLD_TABLE is the table. Q is the alias. TITLE is the column.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';
The query that you have written needs to be modified. I feel that there is no need of using alias keyword in your query.
Without Alias keyword:
UPDATE HOLD_TABLE
SET TITLE='TEST'
WHERE ID=101;
With Alias keyword: (CTE approach)
WITH HOLD_TABLE1 AS
(SELECT * FROM HOLD_TABLE)
UPDATE HOLD_TABLE1
SET TITLE='TEST'
WHERE ID=101;
