Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Updating blank information Expand / Collapse
Author
Message
Posted Tuesday, May 27, 2008 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2008 6:58 AM
Points: 9, 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.
Post #507009
Posted Tuesday, May 27, 2008 9:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #507026
Posted Tuesday, May 27, 2008 9:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #507028
Posted Tuesday, May 27, 2008 9:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2008 6:58 AM
Points: 9, 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
Post #507037
Posted Tuesday, May 27, 2008 10:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:31 PM
Points: 7,135, Visits: 15,141
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?
Post #507053
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse