是否可以在更新期间在case语句中“设置”值,如下所示？
UPDATE TABLE1
CASE WHEN COL1 = 'A' THEN SET COL2 = 10,COL3 = 20,COL4 = 30
WHEN COL1 IN ('B','N') THEN SET COL2 = 1,COL3 = 5,COL4 = 7
WHEN COL1 = 'D' THEN SET COL2 = 11,COL3 = 13,COL4 = 17
ELSE SET COL2 = 0,COL3 = 0,COL4 = 0
END;
解决方法
相应的有效语法将是这样的.
UPDATE TABLE1 SET
COL2 = (CASE WHEN COL1 = 'A' THEN 10
WHEN COL1 IN ('B','N') THEN 1
WHEN COL1 = 'D' THEN 11
ELSE 0
END),COL3 = (CASE WHEN COL1 = 'A' THEN 20
WHEN COL1 IN ('B','N') THEN 5
WHEN COL1 = 'D' THEN 13
ELSE 0
END),COL4 = (CASE WHEN COL1 = 'A' THEN 30
WHEN COL1 IN ('B','N') THEN 7
WHEN COL1 = 'D' THEN 17
ELSE 0
END);
