Update a new column based on existing an column's value

Question

My database is called 'liquorsales' and contains information on liquor sales from Iowa. I'm using SQLiteStudio.

One of the columns is called 'category' and it contains a 7 digit number(datatype is INT) where I noticed a pattern.

Numbers that begin with 101(ie 1010000-1019999) are all Whiskeys products.

Numbers that begin with 102(ie 1020000-1029999) are all Tequila/Mezcals products.

Numbers 1030000-1039999 are Vodka products.

Numbers 1040000-1049999 are Gin products.

etc

I'd like to create a new column called 'alchohol_type' that could read the value in 'category' and then populate 'alchohol_type' with Whiskey or Vodka etc.

I have went ahead and added the new column called alcohol_type.

This is what I plan to run

 UPDATE liquorsales
    SET alchohol_type (case 
        when category > 1009999 AND < 1020000 then 'Whiskey'
        when category > 1019999 AND < 1030000 then 'Tequlia/Mezcal'
        when category > 1029999 AND < 1040000 then 'Vodka'
        when category > 1039999 AND < 1050000 then 'Gin'
        when category > 1049999 AND < 1060000 then 'Brandy'
        when category > 1059999 AND < 1070000 then 'Rum'
      else 'Other (Spirits, Liqueurs, Cocktails)'

end);

I haven't been able to successfully run it using a small sample size of the table. Any suggestions?

Answer 1

UPDATE liquorsales
   SET alchohol_type =
       CASE WHEN category >= 1070000
            THEN 'Other (Spirits, Liqueurs, Cocktails)'
            WHEN category >= 1060000
            THEN 'Rum'
            WHEN category >= 1050000
            THEN 'Brandy'
            WHEN category >= 1040000
            THEN 'Gin'
            WHEN category >= 1030000
            THEN 'Vodka'
            WHEN category >= 1020000
            THEN 'Tequlia/Mezcal'
            WHEN category >= 1010000
            THEN 'Whiskey'
            ELSE 'Other (Spirits, Liqueurs, Cocktails)'
        END