PostgreSQL中UPDATE查询条件写法及除零、空值问题解决

一、正确编写UPDATE查询条件的方法 #

• 基础范围过滤：通过WHERE子句指定更新目标行，避免无WHERE导致全表更新。示例：

  UPDATE your_table SET column = 'new_value' WHERE id = 100;
  

• 关联表更新：基于其他表数据更新时，用FROM子句关联表，比子查询更高效。示例：

  UPDATE t1
  SET t1.col = t2.value
  FROM t2
  WHERE t1.foreign_id = t2.id;
  

• 复杂条件分支：用CASE WHEN处理多场景赋值逻辑，精准控制不同条件下的更新结果。示例：

  UPDATE products
  SET price = CASE
      WHEN stock < 10 THEN price * 1.1
      WHEN stock > 50 THEN price * 0.9
      ELSE price
  END;
  

二、修复你的UPDATE语句（解决空值/除零错误） #

你的原语句存在冗余同表子查询，且未处理all_plats_ok为0或null的异常场景，导致除零报错。以下是两种修正方案：

方案1：用CASE WHEN明确跳过异常行 #

通过条件判断，仅在all_plats_ok有效时计算结果，否则设为null（可替换为你需要的默认值）：

UPDATE summary_all_filter_edit
SET resul_power = CASE
    WHEN all_plats_ok IS NULL OR all_plats_ok = 0 THEN NULL  -- 可改为0等自定义默认值
    ELSE round((((all_plats_ok::numeric - all_plats_no::numeric) / all_plats_ok::numeric) * power::numeric), 2)
END;

方案2：用NULLIF简化除零处理 #

NULLIF会将all_plats_ok为0的情况转为null，PostgreSQL中除以null结果为null，不会触发除零错误：

UPDATE summary_all_filter_edit
SET resul_power = round(
    (((all_plats_ok::numeric - all_plats_no::numeric) / NULLIF(all_plats_ok::numeric, 0)) * power::numeric),
    2
);

性能优化：仅更新有效行 #

如果只想处理all_plats_ok不为null且不为0的行，添加WHERE子句减少不必要的更新操作：

UPDATE summary_all_filter_edit
SET resul_power = round((((all_plats_ok::numeric - all_plats_no::numeric) / all_plats_ok::numeric) * power::numeric), 2)
WHERE all_plats_ok IS NOT NULL AND all_plats_ok != 0;

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