sql - 更新两个表我们得到重复项

Question

我有两张表

emp:

id  name sal deptno
-------------------
1    a    x   10
2    b    x   20
3    c    x   20

部门:

id sal deptno
-------------
1  100 10
2  200 20
3  300 20

这里我想更新 emp 表中的 sal 列

update emp
set sal = d.sal
from emp as e
left join dept as d on e.deptno = d.deptno

执行该查询后我得到

id name sal deptno
------------------
1  a    100 10
2  b    200 20
3  c    200 20

但是我想要

id name sal deptno
------------------
1   a   100 10
2   b   200 20
3   c   300 20

Answer 1

试试这个:

 update emp
 set sal = d.sal
 from emp as e
 left join dept as d on e.id = d.id