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?

I am not sure at all. Sorry, I'm new. When I've looked for solutions/tutorials/examples, I either found the CASE examples to be too basic or too complex. I've found good examples for that use SELECT, but haven't found anything that is for modifying a table or that uses conditions that require a range (ie 1009999> AND <1020000) — Marshall, Sep 26 '22 at 21:40
Seems that the syntax may be off? ... try changing: `category > 1009999 AND < 1020000 then` => `category > 1009999 AND category < 1020000 then`. — Paul T., Sep 27 '22 at 01:44
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. — Community, Sep 27 '22 at 09:58

score 1 · Accepted Answer · answered Sep 27 '22 at 04:07

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

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

1 Answers1