CODEWITHSUNDEEP
apache-spark-sqlazure-databricksdelta-lake
pythonUser
pythonUser

Reputation: 213

How to update a Databricks Delta table with inner join in Databricks using Spark sql

I have to update a table column with inner join with other table.I have tried using the below sql.But i'm getting error in Databricks as (Error in SQL statement: ParseException: mismatched input '' expecting 'WHEN').I tried different ways of updating the table.Can someone help me on this issue how to fix this?

%sql
merge into test a using test_1 b
on (a.id_num=b.id_num)
when matched then
update set a.name=b.name;

Upvotes: 3

Views: 27537

Answers (5)

sandesh dahake
sandesh dahake

Reputation: 1026

If I am getting your question correct you want to use databricks merge into construct to update your table 1 (say destination) columns by joining it to other table 2( source)

MERGE INTO destination 
USING updates
ON destination.primary_key = source.foregin_key
WHEN MATCHED THEN
  UPDATE SET column1= updates.column1

Same query can be extended to insert data as well if no marching row is present in source and destination table.

  • Note- My answer is based on assumption i made at the start. If you can elobrate further, that will be helpful for understanding your problem better

Upvotes: 1

chetan sahu
chetan sahu

Reputation: 41

%%sql
merge into test a using test_1 b
on (a.id_num=b.id_num)
when matched then
update set a.name=b.name;

use double % for this.

Upvotes: 4

Pabbati
Pabbati

Reputation: 4179

query which you are using is correct. Reason for failure is you are not using delta tables, in your case test and test_1 should be delta tables.

Upvotes: 0

Vijay Kumar Sharma
Vijay Kumar Sharma

Reputation: 397

This is because of Databricks Runtime Version, You need to use the Databricks Runtime version 5.1 or above for your query on Delta Table. But for the merge operation, you need to have a Delta table first.

Upvotes: 0

Fabio Schultz
Fabio Schultz

Reputation: 527

Your table must be DELTA format. is it?

I think your command is ok.

https://docs.databricks.com/spark/latest/spark-sql/language-manual/merge-into.html

Upvotes: 1

Related Questions