How do I update column 2 based on column 1, and then column 3 based on column 2

Question

I'm working with an existing table with missing or incorrect data in columns 2 and 3, so I need to update those columns based on column 1 and then column 2 once it's updated.

Column 1 is vehicle "class" (large car, small suv 2wd, pickup 4wd, etc). Column 2 should be a "type_id" (numbered 1-6) based on vehicle class in column 1. And then column 3 is the vehicle "type" (sedan, pickup, etc) based on the type id in column 2.

to update the second column "type_id" I've tried a couple variations of:

UPDATE mytable
SET type_id = CASE 
WHEN "class" = 'Compact Cars' then '1' ELSE type_id
WHEN "class" = 'Midsize Station Wagons' then '3' ELSE type_id
WHEN "class" = 'Minivan - 2WD' then '2' ELSE type_id
WHEN "class" = 'Minivan - 4WD' then '2' ELSE type_id

And so on.

There are about 30 different variables in column 1 that need to correspond with an id number in column 2. From there I need to update the 3rd column with a basic vehicle type that corresponds with the number in column 2.

Any help is appreciated.

Answer 1

I don't really understand the point for an iterative logic here: as I understand your question, you can associate a type id and a type to each class.

A table value might come handy here:

update mytable t
set type_id = x.type_id, type = x.type
from (
    values 
        ('Compact Cars', 1, 'sedan'),
        ('Midsize Station Wagons', 3, 'foo'),
        ('Minivan - 2WD', 2, 'pickup'),
        ('Minivan - 4WD', 2, 'pickup'),
)  x(class, type_id, type)
where x.class = t.class

If you have a large number of transcodifications to perform, it would be simpler to store them all in a table, that you could directly join in your query.