[SOLVED] SQL update multiple rows for a different value based on condition

SQL update multiple rows for a different value based on condition

I need to update values (column 'value') in the table (stage.log) on predefined time interval based on their names (column 'name'). Could someone help me find an easy way to do this?

I use Clickhouse DB.

Here is an example of existing data stored in original table:

SELECT time, name, value 
FROM stage.log
WHERE time >= '2023-07-06 10:00:00'
  AND time <  '2023-07-06 10:00:11'
  AND name IN ('apple', 'orange', 'banana')
ORDER BY time


time                    name    value
----------------------------------------
2023-07-06 10:00:00.001 apple   0.06263
2023-07-06 10:00:01.051 banana  0.06335
2023-07-06 10:00:01.651 orange  0.06332
2023-07-06 10:00:01.690 orange  0.06331
2023-07-06 10:00:02.772 apple   0.0626
2023-07-06 10:00:03.002 apple   0.06261
2023-07-06 10:00:03.111 banana  0.06262
2023-07-06 10:00:03.830 orange  0.06302
2023-07-06 10:00:03.891 orange  0.06301
2023-07-06 10:00:04.330 orange  0.06331
2023-07-06 10:00:05.311 orange  0.06335
2023-07-06 10:00:05.801 apple   0.06258
2023-07-06 10:00:05.951 apple   0.06259
2023-07-06 10:00:06.091 apple   0.0626

Here is my queries that I want to run. My goal is update all values for a predefined different constants based on names in column 'name'.

I have small experience writing queries like ALTER. Please tell me the best way to solve the task. I also note that the total number of names from column "name" that i need to update is quite large, (about 100 pieces), and each name has its own unique constant to update it

ALTER TABLE stage.log
UPDATE value = value - (0.221)
WHERE time >= '2023-07-06 10:00:00'
  AND time <  '2023-07-06 10:00:11'
  AND name = 'apple'


ALTER TABLE stage.log
UPDATE value = value - (-0.45)
WHERE time >= '2023-07-06 10:00:00'
  AND time <  '2023-07-06 10:00:11'
  AND name = 'orange'

ALTER TABLE stage.log
UPDATE value = value - (-0.12)
WHERE time >= '2023-07-06 10:00:00'
  AND time <  '2023-07-06 10:00:11'
  AND name = 'banana'

Solution

UPDATE Table SET value = (CASE when name='banana' THEN value-(-0.12)
when name='apple' THEN value - (-0.221)
when name='orange' THEN value - (-0.45))
ELSE NULL

USE Case to update column values based on column conditions.