cancel
Showing results for 
Search instead for 
Did you mean: 
Get Started Discussions
cancel
Showing results for 
Search instead for 
Did you mean: 

Syntax of UPDATE Command in DataBricks

Peter_Jones
New Contributor III

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
; 

 

 

Best Regards
Peter Jones
2 REPLIES 2

Kaniz
Community Manager
Community Manager

Hi @Peter_Jones

1. Update Command in Databricks SQL: The UPDATE statement in Databricks SQL allows you to modify column values for rows that match a specified predicate.

Here’s the syntax for the UPDATE statement:

UPDATE table_name [table_alias]
SET { { column_name | field_name } = [ expr | DEFAULT } [, ...] }
[WHERE clause]
  • table_name: Identifies the table to be updated (must be a Delta Lake table).
  • table_alias: Optionally define an alias for the table.
  • column_name or field_name: References to columns or fields within a column (for STRUCT types).
  • expr: An arbitrary expression (you can reference table columns to represent the row state prior to the update).
  • DEFAULT: Applies when a DEFAULT expression is defined for the column.

2. Your SQL Statement: Let’s break down your specific SQL statement:

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;
  • You’re updating the dbo.td_address table based on data from the z01_vm_address_01_t1 table.
  • The WHERE clause ensures that only matching rows are updated.

3. Compatibility with Databricks SQL: The UPDATE statement you’ve provided is consistent with Databricks SQL and Delta tables. As long as both tables are Delta Lake tables, this format should work as expected.

Remember to verify the table names, column names, and any other specifics relevant to your environment. If you encounter any issues, feel free to ask for further assistance!

Peter_Jones
New Contributor III

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 

Best Regards
Peter Jones
Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.