CODEWITHSUNDEEP
sqlhivehiveql
Jorge Ortíz
Jorge Ortíz

Reputation: 29

How to update a column using min/max from a different column as conditional on hive

So given the next table:

CREATE TABLE IF NOT exists my_data_base.stg (
    flag INT,
    incidence DATE);
insert into stg (flag, incidence) values (0, '2022-01-01'), (0, '2022-01-02'), (0, '2022-01-03'), (0, '2022-01-04');

I'd would like to change whichever row has the earliest date so that the flag column equals 1 while the rest stay the same, like this.

Incidence Flag
2022-01-01 1
2022-01-02 0
2022-01-03 0
2022-01-04 0

Thank you very much in advance.

Upvotes: 0

Views: 86

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7387

you really dont have to store the like this. But it depends on your requirement.
When you are loading the data, you can calculate the column like this -

SELECT 
flag, case when rs.rn =1 then 1 else 0 end as incidence --stamp least row to 1
FROM
( select flag, row_number() over( order by flag ) rn -- ordering the data so to find least row 
 from my_data_base.stg) rs

OR Else, like comment said, You can use above SQL to stamp the record to 1.

Upvotes: 1

Related Questions