Simple update
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Simple update

  1. #1
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455

    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 09:23 AM.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    There are not, there is only double entries for each.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width