mysql - 使用自连接子查询更新

Question

问题:

A supervisor (employee_id equals to 114) has resigned from the company, and today is the last day of work. With immediate effect, the department he manages, and all those employees he supervises will be taken over by his supervisor

所以我想出的语法是:

UPDATE EMPLOYEE
   SET supervisor_id = (SELECT supervisor_id
                          FROM EMPLOYEE
                         WHERE employee_id = '114'),
 WHERE supervisor_id = '114';

输出:错误 1093 (HY000):您无法在 FROM 子句中指定要更新的目标表“EMPLOYEE”

但是这是错误的。我知道supervisor_id = 100然而我不只是想把 SET supervisor_id = 100因为 100 似乎不公平直接。谁能帮我改正吗？

Answer 1

UPDATE EMPLOYEE a 
  join 
     ( SELECT supervisor_id
            , employee_id  
         FROM EMPLOYEE 
        WHERE employee_id = '114'
     ) b 
    on a.supervisor_id = b.employee_id 
   set a.supervisor_id = b.supervisor_id 

使用此查询