Post reply

Set based Update

  • January 31, 2006 at 6:49 am

    #115983

    I'd do a search on this but it took me about 10 minutes just to get into the site and get to this page. Things seem to be VERY slow today...

    I have a table, ActiveUsers. For the sake of simplicity it has two columns:

    EmpID char(6) PK, Email varchar(255)

    I have a second table Directory. I need to get the Mail field from the Directory and update the ActiveUsers table with it. I can join ActiveUsers to Directory on Empid=EmployeeID.

    Is there a single Update statement that will do this? I can come up with all sorts of ways to extract data into temp tables, match stuff then update the source table, but I know there has to be a single Update that uses a derived query or something like that to do this in one step. I just can't get my head around it.

    Any help would be appreciated.

     

  • January 31, 2006 at 7:11 am

    #617920

    Hello,

    Try this one

    Update ActiveUsers

    Set Email = (Select Mail From Directory Where Empid = EmployeeID)

    Thanks and have a nice day!!!


    Lucky

  • January 31, 2006 at 7:55 am

    #617933

    Update ActiveUsers

    Set Email = (Select Mail From Directory Where Empid = EmployeeID)

     
    Empid is not know in the subquery because ActiveUsers is not a table in the subquery.
     
    I tried
     
    Update ActiveUsers
     
    Set Email=(Select Mail from Directory, ActiveUsers where Empid=EmployeeID)
     
    But then I get "Subquery returned more than 1 value...."
     
     

     

  • January 31, 2006 at 8:00 am

    #617936

    Can you post some sample data of the 2 tables, which will be easy for the workout?

     


    Lucky

  • January 31, 2006 at 8:05 am

    #617937

    Seems like this should work by just doing the update from a join..

    update a

    set    a.email = d.email

    from   ActiveUsers a

    join   Directory d

    on     a.EmpID = d.EmpId

    Would that work for you?

    /Kenneth

  • January 31, 2006 at 9:04 am

    #617957

    That did the trick!

    I was so close...

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply