CODEWITHSUNDEEP
sqlhivehiveql
shashwat_k
shashwat_k

Reputation: 41

Using select statement with if condition in HIve, to populate another column

columns: hp(numerical values), power_rate(empty/NULL).

if hp>70

 power_rate column should be populated as 'powerful'

else

 power_rate should be 'moderate'

How do I achieve this using Hive?

Upvotes: 2

Views: 4074

Answers (2)

INDRAJITH EKANAYAKE
INDRAJITH EKANAYAKE

Reputation: 4274

If you want to populate a column in a new table (without editing the existing table) there is a way,

CREATE TABLE <new_table_name> SELECT hp, CASE WHEN hp>70 THEN 'powerful' ELSE 'moderate' END AS power_rate from <previous_table_name>

Note: Here both tables are creating in the same database. If you want to populate an existing table refer the above answer posted by @leftjoin

Upvotes: 0

leftjoin
leftjoin

Reputation: 38290

Using case statement:

insert overwrite table 
select hp, case when hp>70 then 'powerful' else 'moderate' end as power_rate
  from table

Upvotes: 4

Related Questions