java - 为不同的员工更新 managerId

Question

我有 Reporting_officers 表。表的结构如下

R_id    User_id Manager_id  Managre2_id
1        4       3        3
2        3       2        2
3        5       3        2

现在我想在这个表中更新 user_id 3 到 6，然后它应该反射(reflect)到 manager_id 和 manger2_id 其中有 3如下图

R_id    User_id   Manager_id  Managre2_id
 1          4         6           6
 2          6         2           2
 3          5         6           2

Answer 1

此查询运行完美，您可以在 sql fiddle 上查看 table strutured

table after dml

UPDATE 
  `Reporting_officers`  
SET
  `Manager_id` = NULL
WHERE Reporting_officers.`Manager_id` = 3;

UPDATE 
  `Reporting_officers`  
SET
  `Managre2_id` = NULL
WHERE `Managre2_id` = 3;


UPDATE 
  `Reporting_officers`  
SET
  User_id = 6 
WHERE `User_id` = 3;


UPDATE 
  `Reporting_officers` 
SET
  `Manager_id` = CASE WHEN Manager_id IS NULL  THEN 6 ELSE Manager_id END,
  `Managre2_id` = CASE WHEN Managre2_id IS NULL  THEN 6 ELSE Managre2_id END