mysql update层级

-- 创建部门表
CREATE TABLE Department (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

-- 创建员工表
CREATE TABLE Employee (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Department(id)
);

-- 插入示例数据
INSERT INTO Department (id, name) VALUES (1, 'Sales');
INSERT INTO Employee (id, name, department_id) VALUES (1, 'Alice', 1), (2, 'Bob', 1);

-- 更新部门名称
UPDATE Department
SET name = 'Marketing'
WHERE id = 1;

-- 更新所有员工的部门名称
UPDATE Employee
SET department_id = (SELECT id FROM Department WHERE name = 'Marketing')
WHERE department_id = (SELECT id FROM Department WHERE name = 'Sales');

START TRANSACTION;
SELECT * FROM Department WHERE id = 1 FOR UPDATE;
UPDATE Department SET name = 'Marketing' WHERE id = 1;
COMMIT;

-- 批量更新示例
UPDATE Employee
SET department_id = (SELECT id FROM Department WHERE name = 'Marketing')
WHERE id BETWEEN 1 AND 100;

-- 继续更新下一批
UPDATE Employee
SET department_id = (SELECT id FROM Department WHERE name = 'Marketing')
WHERE id BETWEEN 101 AND 200;