Updating blank information


Updating blank information

Author
Message
J. Cote
J. Cote
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 14
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.
Christopher Stobbs
Christopher Stobbs
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9282 Visits: 2233
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
Numbers / Tally Tables

SQL-4-Life
Christopher Stobbs
Christopher Stobbs
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9282 Visits: 2233
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
Numbers / Tally Tables

SQL-4-Life
J. Cote
J. Cote
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 14
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
Matt Miller (4)
Matt Miller (4)
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51548 Visits: 19472
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)....Tongue

----------------------------------------------------------------------------------
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search