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!