如何编写Oracle UPDATE语句根据消费额设置客户等级？

针对需求，我们可以用Oracle的CASE WHEN条件分支来实现批量更新，语句如下：

UPDATE Customer
SET customer_level = CASE
    WHEN customer_spent >= 100 THEN 'S'
    WHEN customer_spent >= 50 THEN 'A'  -- 无需额外判断<100，前面条件已筛选掉≥100的记录
    WHEN customer_spent >= 0 THEN 'B'
    ELSE customer_level  -- 处理消费额为负数的情况，保持原等级，可按需修改
END;

补充优化说明 #

• 若要避免无意义更新（比如等级已符合规则的记录），可以添加WHERE条件提升性能：

UPDATE Customer
SET customer_level = CASE
    WHEN customer_spent >= 100 THEN 'S'
    WHEN customer_spent >= 50 THEN 'A'
    WHEN customer_spent >= 0 THEN 'B'
    ELSE customer_level
END
WHERE customer_level != CASE
    WHEN customer_spent >= 100 THEN 'S'
    WHEN customer_spent >= 50 THEN 'A'
    WHEN customer_spent >= 0 THEN 'B'
    ELSE customer_level
END;

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