update one hive table based on another table

[#1]

I need to do an update of a column with values from another table

Step1:

execute(set hive.enforce.bucketing=true)by hadoop;

execute(set hive.exec.dynamic.partition.mode=nonstrict)by hadoop;

CREATE TABLE z_test1 ( LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ) clustered by (City) into 3 buckets stored as orc TBLPROPERTIES ('transactional'='true')

Step2:

Insert into z_test1 VALUES ("david","baker","houston","tx"),("rose","crab","houston","tx")

lastname firstname address city david baker houston tx rose crab houston tx

Step3:

execute(set hive.enforce.bucketing=true)by hadoop;

execute(set hive.exec.dynamic.partition.mode=nonstrict)by hadoop;

CREATE TABLE z_test ( LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255), country varchar(255) ) clustered by (City) into 3 buckets stored as orc TBLPROPERTIES ('transactional'='true')

Step4:

Insert into z_test VALUES ("david","burrows","houston","tx","usa")

lastname firstname address city country david burrows houston tx usa

Step5:

update z_test1 set FirstName= z.FirstName from z_test z where z_test1.LastName= z.LastName

Error in step5

Desired Result: davids first name in table z_test1 should be updated as burrows from second table

lastname firstname address city david burrows houston tx rose crab houston tx

[#2]

This syntax is not supported in Hive. The same can be achieve using SQL Merge statement which is implemented in https://issues.apache.org/jira/browse/HIVE-10924.

[#3]

H @Eugene Koifmani, I tried understanding the documentation you have provided but couldn't figure it out what you are saying, I want an hive working code which can replace this

update z_test1 set FirstName= z.FirstName from z_test z where z_test1.LastName= z.LastName

[#4]

Currently there is no way to do this. Once there is a release that includes HIVE-10924 you will be able to do using Merge:

merge into z_test1 using z_test z on z_test1.LastName=z.LastName when matched then update set LastName=z.LastName;

[#5 · Accepted solution]

you can use left join to get this

Step5 can be replace with

create table z_test2 as

select a.LastName,

case when b.FirstName is null

then a.FirstName

else b.FirstName

end as firstname,

a.address, a.city

from z_test1 a left outer join z_test b

on a.LastName = b.LastName