CODEWITHSUNDEEP
hadoopreplacehivenull
Charith Ellepola
Charith Ellepola

Reputation: 302

Insert data to table from another table containing null values and replace null values with the original table 1 values

enter image description here

I want to match first column of both table and insert table 2 values to table 1 . But if Table 2 values are null leave table 1 vlaues as it is .I am using Hive to dothis .Please help.

Upvotes: 1

Views: 261

Answers (1)

notNull
notNull

Reputation: 31490

You need to use coalesce to get non null value to populate b column and case statement to make decision to populate c column.

Example:

hive> select t1.a,
      coalesce(t2.y,t1.b)b,
      case when t2.y is null then t1.c 
      else t2.z 
      end as c  
     from table1 t1 left join table2 t2 on t1.a=t2.x;

+----+-----+----+--+
| a  |  b  | c  |
+----+-----+----+--+
| a  | xx  | 5  |
| b  | bb  | 2  |
| c  | zz  | 7  |
| d  | dd  | 4  |
+----+-----+----+--+

Upvotes: 1

Related Questions