PostgreSQL update column from another column in same table

Question

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

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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

PostgreSQL update column from another column in same table

Sign up