Getting error while executing a MERGE statement in Hive

Question

I am trying to UPDATE table2 using a certain condition, but getting the following error while using a MERGE statement in HIVE.

Someone please look into my error and provide a solution or correct me if any mistakes in the query.

MERGE INTO TABLE1 A1
USING TABLE2 A2
ON A1.PROJECT_ID=A2.PROJECT_ID 
   AND A1.GREENFIELD_STATUS_CODE=A2.GREENFIELD_STATUS_CODE 
   AND A1.COUNTRY_CODE=A2.COUNTRY_CODE 
   AND A1.PROJECT_NME=A2.PROJECT_NME
WHEN MATCHED THEN UPDATE SET A1.DATETIME_STAMP=A2.DATETIME_STAMP;

For the above query I am getting this error:

HiveServer2Error: Error while compiling statement: FAILED: ParseException line 24:31 mismatched input '.' expecting = near 'A1' in WHEN MATCHED THEN clause"

So here I am not able to get what the issue is

Shu Shu · Accepted Answer · 2019-10-11T20:50:31

In your match statement remove A1 alias as Merge statement doesn't expect alias.

Try with this statement:

MERGE INTO TABLE1 A1
USING TABLE2 A2
ON A1.PROJECT_ID=A2.PROJECT_ID 
   AND A1.GREENFIELD_STATUS_CODE=A2.GREENFIELD_STATUS_CODE 
   AND A1.COUNTRY_CODE=A2.COUNTRY_CODE 
   AND A1.PROJECT_NME=A2.PROJECT_NME
WHEN MATCHED THEN UPDATE SET DATETIME_STAMP=A2.DATETIME_STAMP;

Refer to this article for more details regards to Hive-Merge Strategy.

Getting error while executing a MERGE statement in Hive

1 Answers