1

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.

Charith Ellepola
  • 302
  • 2
  • 13
  • You need to perform a `join` on the first column and then use a `case when then` Refer https://stackoverflow.com/questions/32472801/whats-the-best-way-to-write-if-else-if-else-if-else-in-hive – shriyog Dec 19 '18 at 10:21

1 Answers1

1

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  |
+----+-----+----+--+
leftjoin
  • 36,950
  • 8
  • 57
  • 116
notNull
  • 30,258
  • 4
  • 35
  • 50