1

What is wrong with below update query? it gives syntax error the error message is as below:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'a'.

Code:

UPDATE D_date a 
SET d_date_key = b.d_date_key,  
    full_date = b.full_date,    
    day_of_week = b.day_of_week,    
    day_of_week_display = b.day_of_week_display,    
    fiscal_day_number = b.fiscal_day_number 
FROM   
    dbo.[2017 Calendar update] b
WHERE  
    a.d_date_key = b.d_date_key;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RanchiRhino
  • 786
  • 4
  • 21
  • 1
    The error is clearly saying `Incorrect syntax near 'a'` with line no 1. – Yogesh Sharma Apr 13 '18 at 10:58
  • 1
    When asking questions like this, perhaps first ask/answer: Why did you think it **would** work? Based on some other SQL dialect, or random guessing, or what? But presumably not based on reading SQL Server's documentation about syntax, which would be the sensible place to look before posting. – underscore_d Apr 13 '18 at 11:08

2 Answers2

6

You don't alias the table you are updating in the UPDATE clause, you alias in the FROM. The correct syntax would be:

UPDATE a
SET d_date_key =b.d_date_key ,  
    full_date=b.full_date,  
    day_of_week=b.day_of_week,  
    day_of_week_display=b.day_of_week_display,  
    fiscal_day_number=b.fiscal_day_number   
FROM D_date a
     JOIN dbo.[2017 Calendar update] b ON a.d_date_key = b.d_date_key;

It's bad practice, however, to use aliases in ascending alphabetical characters (a,b,c,d), or similar ideas (like T1,T2,T3, etc) as they're meaningless everyone. Use proper aliases. Such as D for D_Date and CU for 2017 Calendar update.

Thom A
  • 88,727
  • 11
  • 45
  • 75
1

Clearly aliases are not allowed in the update part of the update statement in SQL Server. Instead just use the table name:

UPDATE D_date 
    SET d_date_key = b.d_date_key ,  
        full_date = b.full_date,  
        day_of_week = b.day_of_week,  
        day_of_week_display = b.day_of_week_display,  
        fiscal_day_number = b.fiscal_day_number   
    FROM  dbo.[2017 Calendar update] b
    WHERE D_date.d_date_key = b.d_date_key;

Or use an explicit JOIN and reasonable aliases:

UPDATE D_date 
    SET d_date_key = cu.d_date_key ,  
        full_date = cu.full_date,  
        day_of_week = cu.day_of_week,  
        day_of_week_display = cu.day_of_week_display,  
        fiscal_day_number = cu.fiscal_day_number   
    FROM D_date d JOIN
         dbo.[2017 Calendar update] cu
         ON d.d_date_key = cu.d_date_key;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I don't know who downvoted it but it is corret, thanks. – RanchiRhino Apr 13 '18 at 11:06
  • @EricBrandt The second SQL that Gordan has provided does work. You can alias a table in the `FROM` but use it's actual name in the `UPDATE` clause. I believe, however, in T-SQL it's considered bad practice; I think in past versions it could cause odd behaviour; as the table named in the `UPDATE` clause might be treated as a different object to that referenced in the `FROM`. Much like if you had a clause like `FROM Table1 T1, Table1`. `Table1.Col1` would reference the second table. I *think* in older versions of SQL Server the same would happen (not so sure about more recent versions). – Thom A Apr 13 '18 at 11:11
  • @Larnu . . . You *can* alias in the `FROM` and use the table name in the `UPDATE`. The effect is a full Cartesian product, which is usually not what is desired. – Gordon Linoff Apr 13 '18 at 12:21