May 27, 2008 at 9:28 am
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.
May 27, 2008 at 9:42 am
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]
May 27, 2008 at 9:43 am
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]
May 27, 2008 at 9:50 am
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
May 27, 2008 at 10:10 am
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