ClickHouse含CASE与WHERE条件的UPDATE语句报错排查及修正

需求说明 #

更新stage.log表中指定name（如apple、banana，实际约55-70个）的value字段值，仅在特定时间范围内执行更新。当前执行UPDATE语句触发语法错误，需修正。

当前表数据 #

time                    name    value
----------------------------------------
2023-07-06 06:01:00.001 apple   0
2023-07-06 10:00:00.001 apple   0.2
2023-07-06 10:00:01.051 banana  1
2023-07-06 10:00:01.651 orange  0.5
2023-07-06 10:00:01.690 orange  0.5
2023-07-06 10:00:02.772 apple   0.1
2023-07-06 10:00:03.002 apple   0.2
2023-07-06 10:00:03.111 banana  3
2023-07-06 10:00:03.830 orange  5.5
2023-07-06 10:00:03.891 orange  6.5
2023-07-06 10:00:04.330 orange  3.5
2023-07-06 10:00:05.311 orange  3.2
2023-07-06 10:00:05.801 apple   0.7
2023-07-06 10:00:05.951 apple   0.2
2023-07-06 10:00:06.091 apple   0.1
2023-07-07 10:00:01.051 banana  1   

期望更新后的数据 #

time                    name    value
----------------------------------------
2023-07-06 06:01:00.001 apple   0
2023-07-06 10:00:00.001 apple   0.3
2023-07-06 10:00:01.051 banana  6
2023-07-06 10:00:01.651 orange  0.5
2023-07-06 10:00:01.690 orange  0.5
2023-07-06 10:00:02.772 apple   0.2
2023-07-06 10:00:03.002 apple   0.3
2023-07-06 10:00:03.111 banana  8
2023-07-06 10:00:03.830 orange  5.5
2023-07-06 10:00:03.891 orange  6.5
2023-07-06 10:00:04.330 orange  3.5
2023-07-06 10:00:05.311 orange  3.2
2023-07-06 10:00:05.801 apple   0.8
2023-07-06 10:00:05.951 apple   0.3
2023-07-06 10:00:06.091 apple   0.2
2023-07-07 10:00:01.051 banana  1   

错误的SQL语句 #

UPDATE stage.log
SET value = CASE 
    WHEN name='banana'   THEN value -(-5)
    WHEN name='apple'    THEN value -(-0.1)
END
FROM stage.log
WHERE  time >= '2023-07-09 07:01:11.784'
AND    time <= '2023-07-09 07:01:11.998'
AND bt_run_no = 0
AND name in ('banana','apple')

报错信息（中文翻译） #

SQL错误 [62] [07000]: 错误码: 62. DB::Exception: 语法错误: 在位置191 ('FROM')处解析失败（第5行，第1列）: FROM stage.log
WHERE  time >= '2023-07-09 07:01:11.784'
AND    time <= '2023-07-09 07:01:11.998'
AND bt_run_no = 0
AND name in . 期望以下之一: OR, AND, BETWEEN, NOT BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, REGEXP, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, MOD, DIV, IS NULL, IS NOT NULL, token, Comma, IN PARTITION, WHERE. (SYNTAX_ERROR) (版本 23.3.2.37 (官方构建))

修正方案及代码 #

错误原因 #

ClickHouse的UPDATE语法不需要额外的FROM子句，重复指定FROM stage.log会导致语法解析失败。另外当前WHERE子句中的时间范围2023-07-09与示例数据的时间不匹配，建议调整为实际需要的时间区间（比如示例中的2023-07-06 10:00:00到2023-07-06 10:00:06）。

修正后的SQL语句 #

UPDATE stage.log
SET value = CASE 
    WHEN name = 'banana' THEN value + 5  -- 简化value -(-5)为value+5
    WHEN name = 'apple' THEN value + 0.1 -- 简化value -(-0.1)为value+0.1
END
WHERE time >= '2023-07-06 10:00:00'
  AND time <= '2023-07-06 10:00:06'
  AND bt_run_no = 0
  AND name IN ('banana', 'apple')

处理大量name的优化 #

如果需要更新55-70个name，可以用IN子句批量列出，或者结合字典/临时表简化逻辑。例如：

UPDATE stage.log
SET value = CASE 
    WHEN name IN ('banana', 'apple', ...) THEN value + 5 -- 针对某一类name统一加值
    WHEN name IN ('xxx', 'yyy', ...) THEN value + 0.1 -- 另一类加值
    -- 其他规则
END
WHERE time BETWEEN '2023-07-06 10:00:00' AND '2023-07-06 10:00:06'
  AND bt_run_no = 0
  AND name IN ('banana', 'apple', 'xxx', 'yyy', ...) -- 所有需要更新的name

内容的提问来源于stack exchange，提问作者ditrauth