Syntax of UPDATE Command in DataBricks

[Peter_Jones]

Hi All,

I am testing the sql generated by our ETL software to see if it can run on data bricks SQL which I believe is Delta Tables underneath. This is the statement we are testing. As far as I can tell from the manual the from clause is not supported yet in Databricks SQL. But, knowing zero about Databricks, I would not be surprised if I am mistaken. So I thought I would ask here whether this format of update command that takes data from a temporary table and applies it to an existing table via an update like this is supported. Or is there another way to achieve the same?

We are able to customise SQL statements by database in our ETL software. Every statement that is generated is subject to a case statement so that we can support the minor variations between database sql syntax.

Thank you in advance if you are able to offer any assistance in this question.

Best Regards

Peter Jones

update dbo.td_address set
 level_col           = z01_vm_address_01_t1.level_col
,dim_char_ky_fld     = z01_vm_address_01_t1.dim_char_ky_fld
,ss_address_key      = z01_vm_address_01_t1.ss_address_key
,drop_point_id       = z01_vm_address_01_t1.drop_point_id
,address_line_1      = z01_vm_address_01_t1.address_line_1
,address_line_2      = z01_vm_address_01_t1.address_line_2
,address_line_3      = z01_vm_address_01_t1.address_line_3
,address_line_4      = z01_vm_address_01_t1.address_line_4
...removed
,audit_timestamp_01  = z01_vm_address_01_t1.audit_timestamp_01
from dbo.z01_vm_address_01_t1 z01_vm_address_01_t1
where 1=1 
and dbo.td_address.level_col                              = z01_vm_address_01_t1.level_col
and dbo.td_address.dim_char_ky_fld                        = z01_vm_address_01_t1.dim_char_ky_fld
; 

[Peter_Jones]

Hi Kaniz,

thanks for the reply. I am just getting a message that the from statement is invalid. 

It's not explaining much to me.

Our ETL takes the approach of large complex selects in views being read and put into temporary tables (t1 = temporary 1). Then those rows are applied to the target DWH table. This is standard across all our processing. The inserts are working fine. Just the update that is not working yet.

If it is supported to use from in this format I will keep at it and see if I can make it work.

I was thinking maybe this format of update is not yet supported in DataBricks.

Thanks for getting back to me.

Peter