CODEWITHSUNDEEP
sqlhadoophivehiveql
abc
abc

Reputation: 157

Update the "flag" in target based on some rules from 2 source tables in Hive

I want to update my "flag" column in my target table with "Y" and "N" based on rules and data present in the source table.

Rule 1 - If mood is "sad" in source table 1 then update "flag" as "N"

Rule 2- For all the ids and names present in source table 2 update the "flag" as "N".

Rule 3- If "dept" fields contains value as "rty" then also flag should be "N". This scenario is not there in screenshot but please answer this as well.

For all remaining fields the flag should be "Y".

Please find the attached screenshot for clear understanding.

Screenshot

Thanks.

Upvotes: 0

Views: 234

Answers (1)

Fahmi
Fahmi

Reputation: 37483

Try below using case when 

    SELECT a.id,
       a.dept,
       a.mood,
       b.name,
       CASE
           WHEN a.mood='sad' THEN 'N'
           WHEN a.dept='ytr' then 'N'
           WHEN b.id IS NULL THEN 'N'
           ELSE 'Y'
       END AS flag
FROM sourcetable1 a
LEFT JOIN sourcetable2 b ON a.id=cast(b.id as int)

Upvotes: 1

Related Questions