283

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.

DineshDB
  • 5,998
  • 7
  • 33
  • 49
javauser71
  • 4,979
  • 10
  • 27
  • 29
  • 5
    Why do you need to use an alias? It doesn't seem like you need it. – Mark Byers Feb 12 '11 at 23:59
  • 7
    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 Feb 14 '11 at 06:09

2 Answers2

570

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.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 3
    Yes !!! It works. Thanks for the quick response. By any chance do you know why MSSQL server supports such unconventional syntax for update? – javauser71 Feb 14 '11 at 06:02
  • 7
    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 *` –  Aug 30 '12 at 15:25
  • 2
    Nice. This makes it easier to use intellisense in the where clause. – Magnus Jan 29 '15 at 05:36
  • 1
    That's not an alias. That's just the fully qualified 'table.column' name :-/ – ScottWelker Apr 23 '20 at 14:26
  • 1
    @ScottWelker - `HOLD_TABLE` is the table. `Q` is the alias. `TITLE` is the column. – galdin Jun 03 '22 at 11:21
  • Right @galdin. Don't know what I was thinking :( – ScottWelker Jun 03 '22 at 15:30
21

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';
Ryk
  • 3,072
  • 5
  • 27
  • 32
  • 1
    Yes - it also works. But for a JDBC/Java program this is a kind of complex syntax. Thanks for your response. – javauser71 Feb 14 '11 at 06:17