CTE递归select和insert在具有新id和一些更新字段的同一个表中

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    department VARCHAR(100)
);

WITH RECURSIVE employee_tree AS (
    -- Anchor member: select the new employee
    SELECT id, name, manager_id, department
    FROM employees
    WHERE id = 100 -- 假设我们要插入的员工ID为100

    UNION ALL

    -- Recursive member: select the manager and update department
    SELECT e.id, e.name, e.manager_id, 
           CASE 
               WHEN et.department IS NOT NULL THEN et.department 
               ELSE e.department 
           END AS department
    FROM employees e
    INNER JOIN employee_tree et ON e.id = et.manager_id
)
INSERT INTO employees (id, name, manager_id, department)
SELECT id, name, manager_id, department
FROM employee_tree
WHERE id = 100;