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

[JJL]

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!

[Hubert-Dudek · Accepted solution]

I think in MERGE you could replace source_table_reference with view

[oleole]

@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!

[oleole]

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