Updating column values of a table in hive using CTE

Question

I'm a new hive learner. My hive editor does not support update statements. I want to update a column in a table. Let's say I have a table like this.

Input: table_A

field_description	value
field1_name	Age
field2_name	Address

But I want to get something like follows. There are more than 100 rows in table_A

Expected output: table_out

field_description	value
field1	Age
field2	Address

How can I do this using common table expressions? Any suggestions are appreciated.

Answer 1

You can use the Insert overwrite method to achieve the desired result -

INSERT OVERWRITE TABLE table_A
SELECT SUBSTR(field_description, 1, INSTR(field_description, '_') - 1), value
  FROM table_A;