UPDATE with JOIN

-- PostgreSQL — UPDATE ... FROM
UPDATE users u
SET    total_spent = s.total
FROM   (SELECT user_id, SUM(amount) AS total
        FROM   orders
        WHERE  paid_at IS NOT NULL
        GROUP  BY user_id) s
WHERE  u.id = s.user_id;

-- MySQL — UPDATE table1 JOIN table2
UPDATE users u
JOIN  (SELECT user_id, SUM(amount) AS total
       FROM   orders
       WHERE  paid_at IS NOT NULL
       GROUP  BY user_id) s
   ON s.user_id = u.id
SET u.total_spent = s.total;

-- SQL Server / Oracle (using MERGE — see separate snippet) or with a CTE
WITH s AS (SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id)
UPDATE u SET total_spent = s.total FROM users u JOIN s ON s.user_id = u.id;