cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Does Spark SQL can perform UPDATE with INNER JOIN and LIKE with '%' + [column] + '%' ?

JJL
New Contributor II

Hi All,

I came from MS SQL and just started to learning more about Spark SQL

Here is one part that I'm trying to perform. In MS SQL, it can be easily done, but it seems like it doesn't in Spark

So, I want to make a simple update to the record, if the color name can be found anywhere before the '(' syntax

MASTER.Product Table contain name of the product name like 'Spiderman Blue (Dark Blue)'

LooUp.Lookup_Table_Colors Table Contain list of list of colors like 'Red', 'Blue', 'Green', or etc

Below statement basically will find 'Spiderman Blue (Dark Blue)' and fill [Product_Color] as 'Blue'

This statement works in MS SQL, but it error out on Spark SQL

UPDATE P

SET Product_Color = C.Color_Name

FROM MASTER.Product P

INNER JOIN LooUp.Lookup_Table_Colors C 

ON P.Name LIKE '% ' + C.Color_Name + ' % (%';

Error Message: Error in SQL statement: ParseException:

mismatched input 'FROM' expecting {<EOF>, ';'}(line 3, pos 1)

So I went different route, which is using merge... It didn't error out, but it didn't update any record

MERGE into MASTER.Product P

using LooUp.Lookup_Table_Colors C ON P.Name LIKE '% ' + C.Color_Name + '% (%'

WHEN MATCHED THEN UPDATE set P.Product_Color =C.Color_Name;

Is there way to do this in Spark SQL? If these syntax doesn't exist in Spark SQL, then is there a different syntax that I could use?

Thank you in advance!

1 ACCEPTED SOLUTION

Accepted Solutions

Hubert-Dudek
Esteemed Contributor III

I think in MERGE you could replace source_table_reference with view

View solution in original post

3 REPLIES 3

Hubert-Dudek
Esteemed Contributor III

I think in MERGE you could replace source_table_reference with view

oleole
Contributor

@Hubert Dudek​ Hello, I'm having the same issue with using UPDATE in spark sql and came across your answer. When you say "replace source_table_reference with view" in MERGE, do you mean to replace "P" with "VIEW" that looks something as below:

%sql
MERGE into MASTER.Product VIEW
using LooUp.Lookup_Table_Colors C ON VIEW.Name LIKE '% ' + C.Color_Name + '% (%'
WHEN MATCHED THEN UPDATE set VIEW.Product_Color =C.Color_Name;

Thanks in advance for your help!

I think that's what you meant - it worked for me! Thanks for sharing your knowledge @Hubert Dudek​ 

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!