Simple update

[irehman]

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

[marist89]

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...)

[irehman]

There are not, there is only double entries for each.

[marist89]

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.

[slimdave]

maybe ...

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.