Hive replace all row values of table, where it matches value of another table

Question

Table 1:

name    first_name  last_name

xyz          abc          def

Table 2:

name           id   

xyz             1   
abc             2   
def             3   

Expected Output Table:

name    first_name  last_name

1           2            3

I tried to keep case statement replacing values by joining table

Answer 1

This will work

select 
case when t1.name = t2.emp_name then t2.id end as name_1,
case when t1.first_name = t3.emp_name then t3.id end as name_2,
case when t1.last_name = t4.emp_name then t4.id end as name_3
from table1 as t1

join table2 as t2
on t1.name = t2.emp_name 

join table2 as t3
on t1.first_name = t3.emp_name

join table2 as t4
on t1.last_name = t4.emp_name