-
Simple update
I have a table T1 with following columns and data
ID----Name-----Salary-----Dept
1.....Joe
2.....Kelly
3.....Sam........300.......10
4.....Joe........500.......20
5.....Kelly......700.......20
Now I want to run update so Joe's salary and dept should be equal to other Joe which is 500 and 20, and Kelly's salary should be equal to other kelly's salary and dept.
I Have over 2000 rows that needs to be update, so I have to to run single statement.
Thanks in Advance
-
What if there are three joes, one with null salary, one with $300, and one with $500?
(Out of them all, I'd prefer to be Kelly...)
-
There are not, there is only double entries for each.
-
Code:
SQL> select * from xyz;
ID NAME SAL
---------- -------------------- ----------
1 joe
2 sally
3 pete
4 pete 2222
5 joe 3333
6 sally 7777
7 kelly 4444
7 rows selected.
SQL> update xyz x
set sal = (
select max(sal)
from xyz y
where sal is not null and y.name = x.name)
/
7 rows updated.
SQL> select * from xyz;
ID NAME SAL
---------- -------------------- ----------
1 joe 3333
2 sally 7777
3 pete 2222
4 pete 2222
5 joe 3333
6 sally 7777
7 kelly 4444
7 rows selected.
-
maybe ...
Code:
update xyz x
set sal = (
select sal
from xyz y
where y.id!=x.id and y.name = x.name)
where sal is null
/
Might be a little quicker, if id is indexed and sal isn't.