ziggy
ziggy

Reputation: 1538

PostgreSQL update column from another column in same table

table called labor. i have a column called code which has hundreds of code but the codes only start with a 1, 2 or 4. I am trying to update a new column in the same table that contains the first digit in each code.

 existing column  column to update(exists but is empty)
 code             type_
 113249            1
 135435            1   
 254646            2
 208984            2
 409098            4
 429540            4

the code column is numeric and the type_ column is numeric as well

here is what I have tried but it has not worked

update labor set type_ = case code
            when code::text like '%1' then 1 
            when code::text like '%2' then 2
            when code::text like '%4' then 4
            end

Upvotes: 4

Views: 5629

Answers (1)

Roman Tkachuk
Roman Tkachuk

Reputation: 3266

update labor set type_ = left(code::text, 1)::int8

Upvotes: 3

Related Questions