Databricks- Spark SQL Update statement error-CodePudding

This is a pretty straightforward update statement that works on SQL Server DB and I have re-written it in Databricks which is not working, Can you provide your suggestions?

update 
a

set 
    composite_account_key=nvl(e.account_key,0)
edw.account_fact a
join edw.account_dim b on (a.account_key=b.account_key)
join vw_account_hier c on (b.accountcode=c.accountcode)
join edw.analysis_codes_dim d on (d.anlys_code_dimkey=a.anlys_code_dimkey and c.atomic_anlys_appl_cde=d.anlys_appl_cde)
join vw_composite e on (c.edw_c_account_code=e.edw_c_account_code)
where 
a.timekey='95'

ParseException:[PARSE_SYNTAX_ERROR] Syntax error at or near 'from'(line 5, pos 0)

CodePudding user response：

The syntax of update statement in Databricks SQL does not support using from parameter.
You can create a temporary view from the result of all the join operations and use this view in the update statement directly instead.
The following is the demonstration of the same. I have the result of my join query as shown below:

enter image description here

When I try to use from parameter directly in update statement (update id value to 10 wherever it is 1 from join result), I get the same error.

enter image description here

So, I have created a view first and then used it in update query to get the result.

%sql

--CREATE TEMPORARY VIEW for_updt as (select a.id,a.gname,b.team from demo as a join demo1 as b on a.id=b.id );

update demo set id=10 where id in(select id from for_updt where) and (demo.id=1)

enter image description here