SQL Server中如何向已存在的manager列插入指定数值

你已通过以下语句完成了manager列的新增：

ALTER TABLE employee ADD manager INT NULL;

你需要为已有的employee表行更新对应manager值，而非插入新行，所以使用UPDATE语句完成操作即可。

你提供的employee表结构参考：

假设你给出的manager值列表[4、3、7、8、4、12、10、10、6、NULL、3、10]是按表中emp_id（员工主键）从1到12的顺序一一对应，可选择以下两种方案执行：

方案1：单条语句逐个更新（适合小数据量场景，逻辑直观） #

UPDATE employee SET manager = 4 WHERE emp_id = 1;
UPDATE employee SET manager = 3 WHERE emp_id = 2;
UPDATE employee SET manager = 7 WHERE emp_id = 3;
UPDATE employee SET manager = 8 WHERE emp_id = 4;
UPDATE employee SET manager = 4 WHERE emp_id = 5;
UPDATE employee SET manager = 12 WHERE emp_id = 6;
UPDATE employee SET manager = 10 WHERE emp_id = 7;
UPDATE employee SET manager = 10 WHERE emp_id = 8;
UPDATE employee SET manager = 6 WHERE emp_id = 9;
UPDATE employee SET manager = NULL WHERE emp_id = 10;
UPDATE employee SET manager = 3 WHERE emp_id = 11;
UPDATE employee SET manager = 10 WHERE emp_id = 12;

方案2：批量CASE更新（只需执行一次，效率更高，兼容所有主流SQL数据库） #

UPDATE employee
SET manager = CASE emp_id
    WHEN 1 THEN 4
    WHEN 2 THEN 3
    WHEN 3 THEN 7
    WHEN 4 THEN 8
    WHEN 5 THEN 4
    WHEN 6 THEN 12
    WHEN 7 THEN 10
    WHEN 8 THEN 10
    WHEN 9 THEN 6
    WHEN 10 THEN NULL
    WHEN 11 THEN 3
    WHEN 12 THEN 10
END
WHERE emp_id BETWEEN 1 AND 12;

注意：如果你的manager值顺序不是对应emp_id 1-12的顺序，请调整CASE中的WHEN匹配值和对应manager值，保证和实际员工对应，避免赋值错误

内容的提问来源于stack exchange，提问作者Rohan Srivastwa