Post reply

update column from another table

  • May 18, 2009 at 7:06 am

    #131677

    hi everybody.

    i got a problem with update a table and i hoop that somebody can help me?

    i have a database with more then 50 tables.

    two of them are (person en functionofperson )

    in table person i have (Pid, pname, postTo,...) and in table functionofperson i have (pid, orgid, functionid).

    exmp.

    in first table: (person)

    1 - smith -

    2 - black -

    3 - brown - -1 => (-1 means that post has to go to somewhere else and not on his/ her work)

    .....

    in second table for ex. you can see:

    1 - 10- 8

    2 - 15 - 50

    .....

    now i am trying to update table person with this query:

    update Person

    set postto = (select orgid from functionofperson where functionofperson.pid = person.pid)

    i got error:

    sub query returned more then 1 value.....

    what is not correct?

    any idea??

    thanks

    Mori

  • May 18, 2009 at 7:20 am

    #996302

    I guess it's exactly what the error message states: the query returned more than 1 row.

    Check if you have duplicate pid in functionofperson:

    SELECT pid, COUNT(*)

    FROM functionofperson

    GROUP BY pid

    HAVING COUNT(*) > 1

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • May 18, 2009 at 7:29 am

    #996309

    yes there is 7 double records but i can not delete them because a person can have more then one function in a company.

    (i suppose)

    and now?

    distinct also doesn't help.:doze:

  • May 18, 2009 at 7:33 am

    #996313

    You have duplicates, but why not a straight update if you don't have them?

    update Person

    set postto = orgid

    from functionofperson

    where functionofperson.pid = person.pid

    No need for a subquery.

    You could add a subquery if you needed to do something like:

    update Person

    set postto = ( select top 1

    orgid

    from functionofperson fp

    inner join person p

    where fp.pid = p.person.pid

    order by p.orgid

    )

  • May 18, 2009 at 7:35 am

    #996315

    I think this is a database design problem: if a person can have more than 1 function, maybe person table is not the right table to store that information. Probably you have to display this information on screen and for simplicity you just want to get the first function, ignoring any others. In this case, you can add TOP 1 to your subquery, but I would separate data from application and accoplish this task on the app side.

    Regards

    Gianluca

    -- Gianluca Sartori

  • May 18, 2009 at 7:43 am

    #996321

    Steve Jones - Editor (5/18/2009)

    You have duplicates, but why not a straight update if you don't have them?

    update Person

    set postto = orgid

    from functionofperson

    where functionofperson.pid = person.pid

    No need for a subquery.

    Obviously in this case there's a db design issue and working with subqueries or UPDATE...FROM doesn't make any difference on the design side, but please note that if you used this syntax you would never have discovered that something was designed poorly: the update wouldn't raise errors, but update the destination column with unpredictable data.

    -- Gianluca Sartori

  • May 18, 2009 at 7:50 am

    #996335

    thanks everybody.

    that's work. 🙂

Viewing 7 posts - 1 through 7 (of 7 total)

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