Updating two tables getting duplicates

Question

I have two tables like

emp

:

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

dept

:

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

Here I want to update a column sal

in a tableemp

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

After executing this request, I get

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

but I want

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

+3

1 answer

Asromi rOmi · Answer 1 · 2014-10-13T04:57:17+0000

try this:

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