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;
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.
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;
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).
FROM and use the table name in the UPDATE. The effect is a full Cartesian product, which is usually not what is desired.
Incorrect syntax near 'a'with line no 1.