我的查询哪里出问题了？

CREATE PROCEDURE process_flight_log_staging_doug()

BEGIN

UPDATE `flight_log_staging_emt` SET `off_z` = 
CASE 'offz'
WHEN `off_tz`='EDT' THEN `off_lcl`+INTERVAL 4 HOUR
WHEN `off_tz`='CDT' OR `off_tz`='EST' THEN `off_lcl`+INTERVAL 5 HOUR
WHEN `off_tz`='MDT' OR `off_tz`='CST' THEN `off_lcl`+INTERVAL 6 HOUR
WHEN `off_tz`='PDT' OR `off_tz`='MST' THEN `off_lcl`+INTERVAL 7 HOUR
WHEN `off_tz`='PST' THEN `off_lcl`+INTERVAL 8 HOUR
ELSE `off_z` 
END AS 'offz';

UPDATE `flight_log_staging_emt` SET `on_z` = 
CASE 'onz'
WHEN `on_tz`='EDT' THEN `on_lcl`+INTERVAL 4 HOUR
WHEN `on_tz`='CDT' OR `on_tz`='EST' THEN `on_lcl`+INTERVAL 5 HOUR
WHEN `on_tz`='MDT' OR `on_tz`='CST' THEN `on_lcl`+INTERVAL 6 HOUR
WHEN `on_tz`='PDT' OR `on_tz`='MST' THEN `on_lcl`+INTERVAL 7 HOUR
WHEN `on_tz`='PST' THEN `on_lcl`+INTERVAL 8 HOUR
ELSE `on_z` 
END AS 'onz';

END

3 queries executed, 0 success, 3 errors, 0 warnings

Query: CREATE PROCEDURE process_flight_log_staging_doug() BEGIN UPDATE \`flight_log_staging_emt\` SET \`off_z\` = CASE 'offz' WHEN \`off_tz\`...

Error Code: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'offz'' at line 13

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.146 sec

\-----------------------------------------------------------

Query: UPDATE \`flight_log_staging_emt\` SET \`on_z\` = CASE 'onz' WHEN \`on_tz\`='EDT' THEN \`on_lcl\`+INTERVAL 4 HOUR WHEN \`on_tz\`='CDT' OR \`...

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'onz'' at line 9
Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.093 sec
-----------------------------------------------------------
Query: END
Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1
Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.098 sec

解决方案

你在 SET 子句中没有使用 AS，因此你不应该有 AS 'offz' 和 AS 'onz'。它们只用于在 SELECT、FROM 和 jOIN 子句中提供别名。

也没必要分成两个独立的查询，你可以在一个 UPDATE 中设置多列。

有两种形式的 CASE 表达式：

1. CASE <expression> WHEN <value1> THEN <result1> WHEN <value2> THEN <result2> ... END
2. CASE WHEN <condition1> THEN <result1> WHEN <condition2> THEN <result2> ... END

你混用了这两种形式。你的 WHEN 子句中包含条件，所以你应该使用形式2，且在 CASE 之后不要再带上列名。

你也可以使用 IN 来简化将一个值与多项条件进行比较。

UPDATE `flight_log_staging_emt` 
SET `off_z` = 
    CASE
        WHEN `off_tz` = 'EDT' THEN `off_lcl`+INTERVAL 4 HOUR
        WHEN `off_tz` IN ('CDT', 'EST') THEN `off_lcl`+INTERVAL 5 HOUR
        WHEN `off_tz` IN ('MDT', 'CST') THEN `off_lcl`+INTERVAL 6 HOUR
        WHEN `off_tz` IN ('PDT', 'MST') THEN `off_lcl`+INTERVAL 7 HOUR
        WHEN `off_tz` = 'PST' THEN `off_lcl`+INTERVAL 8 HOUR
        ELSE `off_z` 
    END,
    `on_z` =
    CASE
        WHEN `on_tz` = 'EDT' THEN `on_lcl`+INTERVAL 4 HOUR
        WHEN `on_tz` IN ('CDT', 'EST') THEN `on_lcl`+INTERVAL 5 HOUR
        WHEN `on_tz` IN ('MDT', 'CST') THEN `on_lcl`+INTERVAL 6 HOUR
        WHEN `on_tz` IN ('PDT', 'MST') THEN `on_lcl`+INTERVAL 7 HOUR
        WHEN `on_tz` = 'PST' THEN `on_lcl`+INTERVAL 8 HOUR
        ELSE `on_tz` 
    END;