多表关联 UPDATE

[JasonWu73]

-- 各用户消费的总金额
SELECT
  user_name,
  SUM(pay_money) AS total_money
FROM
  users_buy
GROUP BY
  user_name;

-- 根据用户消费的总金额，更新用户积分（消费总金额的 10% 作为增加的积分）
UPDATE
  users_score a
  JOIN (
    SELECT
      user_name,
      SUM(pay_money) AS total_money
    FROM
      users_buy
    GROUP BY
      user_name
  ) b ON b.user_name=a.user_name
SET a.user_score=a.user_score+b.total_money*0.1;