Updating blank information

  • Hello,

    Given the following information:

    Table:test_2009

    organization organization_name

    1001

    3099

    1032 EXECUTIVE CTYG

    Table: org_2008

    organization organization_name

    1001 EXEC CCL

    3099 HOLIDAYS

    1032 EXECUTIVE CTYG

    I am trying to write a query that will update the blank organization_names in test_2009 with the information in the org_2008 table.

    Here is the code i'm using:

    update test_2009 /*This is the table that is being updated*/

    set organization_name = /* This is the field that is being updated */

    (select e.organization_name

    from org_2008 as e

    where (organization in

    (select organization

    from test_2009 as n

    where n.organization_name='' and n.organization=e.organization)

    )

    )

    but i'm getting the error:

    Msg 512, Level 16, State 1, Line 8

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.

    I understand why i'm getting the error, but I can't figure out how to get it to return only one response so I can do the update properly. Can someone point out my error?

    Thanks.

  • HI there,

    Have you tried using a join instead:

    update [2009]

    set [2009].organization_name = e.organization_name

    FROM test_2009 [2009]

    INNER JOIN org_2008 [2008]

    ON [2008].organization_name = [2009].organization_name

    WHERE [2009].organization_name=''

    Thaks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • ooops,

    The join should be on Organization and not on Organization_name

    ....

    thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • You know, I knew it had to be something simple. One of my umpteen million test iterations had something close.

    But of course, close only counts in hand-shoes and horse grenades.

    In any case, thank you Chris for your help, that solved the problem.

    JC

  • J. Cote (5/27/2008)


    You know, I knew it had to be something simple. One of my umpteen million test iterations had something close.

    But of course, close only counts in hand-shoes and horse grenades.

    In any case, thank you Chris for your help, that solved the problem.

    JC

    Don't forget heavy artillery (for the close enough list)....:P

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

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