mysql 关联表更新数据库表

-- users 表结构
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    total_amount DECIMAL(10, 2)
);

-- orders 表结构
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2)
);

UPDATE users
JOIN (
    SELECT user_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
) AS order_totals
ON users.id = order_totals.user_id
SET users.total_amount = order_totals.total_amount;

START TRANSACTION;

UPDATE users
JOIN (
    SELECT user_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
) AS order_totals
ON users.id = order_totals.user_id
SET users.total_amount = order_totals.total_amount;

COMMIT;

CREATE INDEX idx_user_id ON orders(user_id);