-
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
Last edited by irehman; 12-02-2003 at 10:23 AM.
-
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...)
Jeff Hunter
-
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.
Jeff Hunter
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|