mysql两张表更新数据库

-- 创建示例表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    status VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 插入示例数据
INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');
INSERT INTO orders (id, user_id, status) VALUES (101, 1, 'Pending');

-- 更新操作
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.email = 'new_email@example.com', o.status = 'Completed'
WHERE u.id = 1;

START TRANSACTION;

UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.email = 'new_email@example.com', o.status = 'Completed'
WHERE u.id = 1;

COMMIT;

-- 创建索引
CREATE INDEX idx_user_id ON orders(user_id);

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;