mysql 联表修改

-- 创建示例表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    total_price DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Product A', 10.00),
(2, 'Product B', 20.00);

INSERT INTO orders (order_id, product_id, quantity, total_price) VALUES
(1, 1, 2, 20.00),
(2, 2, 1, 20.00);

-- 联表修改示例：更新产品价格并重新计算订单总价
UPDATE orders o
JOIN products p ON o.product_id = p.product_id
SET o.total_price = p.price * o.quantity;

START TRANSACTION;

UPDATE orders o
JOIN products p ON o.product_id = p.product_id
SET o.total_price = p.price * o.quantity;

COMMIT;