Hive update a column from another table

[#1]

Hi,

I have a clustered bucketted table, with ACID enabled

Does hive supports updates from another table?

This is something I am trying to achieve, updating a column values from another table

UPDATE transaction_bak

SET fl_t_order = tt.t_order

FROM trans_temp tt

WHERE transaction_bak.id = tt.id;

I tried with left join, but no success, getting ParseException line 2:0 missing SET at 'left'

UPDATE transaction_bak

LEFT OUTER JOIN trans_temp tt

SET fl_t_order = tt.t_order

WHERE transaction_bak.id = tt.id;

Any idea

[#2]

Hive does not support this directly but you can use SQL Merge statement to achieve this: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-Merge

[#3]

Thanks Eugene for swift reply.

We are running on HDP 2.5 and MERGE is available in 2.6. What options do I have?

Thanks

MB

[#4]

@M B. Here is an older, pre-merge blog on how to handle incremental changes in Hive. https://hortonworks.com/blog/four-step-strategy-incremental-updates-hive/

[#5]

I am also facing the same problem