ziggy ziggy - 4 years ago 186
SQL Question

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

Answer Source
update labor set type_ = left(code::text, 1)::int8
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download