[FIXED] Sqlite3 Python - Adding values to a column depending on values in another column

Issue

I have the database CarSharing, which contains the table CarSharing.

create_table = '''CREATE TABLE CarSharing (
        Id INTEGER,
        Timestamp DATETIME,
        Season TEXT,
        Holiday TEXT, 
        Working_Day TEXT,
        Weather TEXT,
        Temp DECIMAL, 
        Temp_Feel DECIMAL, 
        Humidity INTEGER, 
        Windspeed DECIMAL, 
        Demand DECIMAL
        ); '

I have altered the table to contain a new column called temp_category:

addcolumn = "ALTER TABLE CarSharing ADD COLUMN temp_category varchar(10)"

I need to now add string values to this temp_category column depending on the values in the Temp_Feel column. So that when Temp_Feel is less than 10, the value in the temp_category column is "Cold", if it's between 10-25 the the value is "Mild" and if greater than 25, then it is "Hot".

I have looked at examples online of vaguely similar situations but cannot seem to find one that fits this.

columndata = "update CarSharing set temp_category = Case Temp_Feel WHEN 10> THEN 'Cold', WHEN 10<= 25>= THEN 'Mild', WHEN 25< THEN 'Hot'"

cur.execute(columndata)

I have a feeling I need to use something similar to this but I cannot seem to get the formatting right.

Solution

Use correct syntax for the CASE expression:

UPDATE CarSharing 
SET temp_category = CASE 
  WHEN Temp_Feel < 10 THEN 'Cold'
  WHEN Temp_Feel <= 25 THEN 'Mild'
  WHEN Temp_Feel > 25 THEN 'Hot'
END
WHERE Temp_Feel IS NOT NULL;

Or:

UPDATE CarSharing 
SET temp_category = CASE 
  WHEN Temp_Feel < 10 THEN 'Cold'
  WHEN Temp_Feel <= 25 THEN 'Mild'
  ELSE 'Hot'
END
WHERE Temp_Feel IS NOT NULL;

Answered By - forpas