mysql 多表修改

-- 创建示例表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT
);

CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    stock INT
);

-- 插入示例数据
INSERT INTO orders (order_id, product_id, quantity) VALUES (1, 101, 2);
INSERT INTO inventory (product_id, stock) VALUES (101, 10);

-- 多表修改示例
UPDATE orders o
JOIN inventory i ON o.product_id = i.product_id
SET i.stock = i.stock - o.quantity
WHERE o.order_id = 1;

START TRANSACTION;
UPDATE orders o
JOIN inventory i ON o.product_id = i.product_id
SET i.stock = i.stock - o.quantity
WHERE o.order_id = 1;
COMMIT;

START TRANSACTION;
SELECT stock FROM inventory WHERE product_id = 101 FOR UPDATE;
UPDATE orders o
JOIN inventory i ON o.product_id = i.product_id
SET i.stock = i.stock - o.quantity
WHERE o.order_id = 1;
COMMIT;

UPDATE orders o
JOIN inventory i ON o.product_id = i.product_id
SET i.stock = i.stock - o.quantity
WHERE o.order_id = 1 AND i.product_id = 101;