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

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?

CodePudding user response：

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