如何按主键code关联两表计算cost_col差值更新Difference字段

实现前提 #

• 两表唯一匹配依据为共有主键code，绝对不能依赖行存储顺序做匹配，否则会出现大面积数据错配
• 差值计算逻辑默认取valuation_average表cost_col减去valuation_cost表对应cost_col的值，业务需要反向差值可直接调换两个字段的计算顺序
• 正式更新前必须先做关联查询校验，确认匹配关系和计算结果符合预期后再执行写入

第一步：预校验关联结果 #

先执行以下查询，核对返回的关联数据、计算出的差值是否符合业务预期：

SELECT
  a.code,
  a.cost_col AS avg_cost,
  b.cost_col AS real_cost,
  a.cost_col - b.cost_col AS pre_cal_diff
FROM valuation_average a
JOIN valuation_cost b
  ON a.code = b.code;

若查询结果出现code缺失、数值和业务认知不符的情况，先排查两表主键数据是否一致，不要直接执行更新操作

第二步：执行更新写入Difference字段 #

根据你使用的数据库类型，选择对应的更新语句执行即可：

MySQL / MariaDB #

UPDATE valuation_average a
JOIN valuation_cost b
  ON a.code = b.code
SET a.Difference = a.cost_col - b.cost_col;

PostgreSQL / SQL Server #

UPDATE valuation_average a
SET Difference = a.cost_col - b.cost_col
FROM valuation_cost b
WHERE a.code = b.code;

Oracle #

UPDATE valuation_average a
SET Difference = (
  SELECT a.cost_col - b.cost_col
  FROM valuation_cost b
  WHERE b.code = a.code
)
WHERE EXISTS (
  SELECT 1
  FROM valuation_cost b
  WHERE b.code = a.code
);

特殊场景适配 #

如果存在valuation_average中的code在valuation_cost表无匹配记录的场景，可根据业务需求调整关联逻辑：

• 若需要保留无匹配记录的Difference为NULL，维持INNER JOIN逻辑即可
• 若需要无匹配记录时按特定规则赋值，可改用LEFT JOIN结合COALESCE函数处理，例如无匹配时取b表cost_col为0计算差值，MySQL写法示例：

UPDATE valuation_average a
LEFT JOIN valuation_cost b
  ON a.code = b.code
SET a.Difference = a.cost_col - COALESCE(b.cost_col, 0);

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