mysql - 如何通过比较两个表中的两列来更新表中的列

Question

我有两个表 f1 和 s1。我必须比较两个表的波段列，如果相同，则我必须更新 s1 的状态列中的“无变化”，如果不相同，则更新“等级变化”。

mysql> select * from f1;+--------+------+------+-------------+| emp_id | name | band | project_des |+--------+------+------+-------------+| 1      | A    | u1   | IT          || 2      | B    | u2   | COMP        || 4      | D    | u3   | COMP        || 5      | E    | u2   | ELECTRICAL  || 6      | F    | u2   | IT          || 8      | H    | p1   | MECH        |+--------+------+------+-------------+6 rows in set (0.00 sec)mysql> select * from s1;+--------+------+------+-------------+--------------+--------+| emp_id | name | band | project_des | check_status | status |+--------+------+------+-------------+--------------+--------+| 1      | A    | u1   | IT          | present      | NULL   || 2      | B    | u1   | COMP        | present      | NULL   || 3      | C    | p2   | COMP        | NULL         | NULL   || 4      | D    | p2   | ELECTRICAL  | present      | NULL   || 5      | E    | p3   | IT          | present      | NULL   || 7      | G    | p3   | IT          | NULL         | NULL   || 8      | H    | p1   | COMP        | present      | NULL   |+--------+------+------+-------------+--------------+--------+7 rows in set (0.00 sec)mysql> update s1 inner join f1 on 's1.band' != 'f1.band' set status='grade change';Query OK, 4 rows affected (0.06 sec)Rows matched: 7  Changed: 4  Warnings: 0mysql> select * from s1;+--------+------+------+-------------+--------------+--------------+| emp_id | name | band | project_des | check_status | status       |+--------+------+------+-------------+--------------+--------------+| 1      | A    | u1   | IT          | present      | grade change || 2      | B    | u1   | COMP        | present      | grade change || 3      | C    | p2   | COMP        | NULL         | grade change || 4      | D    | p2   | ELECTRICAL  | present      | grade change || 5      | E    | p3   | IT          | present      | grade change || 7      | G    | p3   | IT          | NULL         | grade change || 8      | H    | p1   | COMP        | present      | grade change |+--------+------+------+-------------+--------------+--------------+7 rows in set (0.00 sec)

请帮我找出我做错了什么。

Answer 1

在 SQL Server 上，试试这个:

update s1 set
    status = case when f1.band = s1.band
        then 'no change' else 'grade change' end
from s1 join f1 on f1.emp_id = s1.emp_id

或者，在 mySQL 上，如果该语法不起作用:

update s1 set status = 
    (Select case when band = s1.band
     then 'no change' else 'grade change' end
     From f1 where emp_id = s1.emp_id)