Sub-Query Error

17 Aug 2006 2:32 PM

Kevin

I am getting the following sub-query error msg,

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 am getting the msg, just not how to resolve the issue.
The SR_Master_Data table has the following fields
ServiceRequestName
StatusID
ContactResourceID
RequestorResourceID

And has a one to one relationship with
BOSC.BoscReporting.dbo.srm_service_requests table. This table has a one to
many relationship with BOSC.BoscReporting.dbo.srm_sr_contact_xref

I need the sub queries to return the cont.c_contact_resource_id for the
current record being updated. I am not sure how to do this.

UPDATE dbo.SR_Master_Data
SET     ServiceRequestName = sr.c_description,
    StatusID = sr.c_status,
    ContactResourceID = (
        SELECT cont.c_contact_resource_id
        FROM BOSC.BoscReporting.dbo.srm_sr_contact_xref cont
        WHERE cont.n_contact_role_id IN('3')),
    RequestorResourceID = (
        SELECT cont.c_contact_resource_id
        FROM BOSC.BoscReporting.dbo.srm_sr_contact_xref cont
        WHERE cont.n_contact_role_id IN('1')),

FROM BOSC.BoscReporting.dbo.srm_service_requests sr
    LEFT OUTER JOIN BOSC.BoscReporting.dbo.srm_sr_contact_xref cont
    ON sr.c_service_req = cont.c_service_req
WHERE sr.c_service_req = dbo.SR_Master_Data.ServiceRequestNumber

17 Aug 2006 2:49 PM

Madhivanan

The subquery should return only one row/ In your case it returns more
than one row. Use top 1 or max or min function

Madhivanan

Kevin wrote:
Show quote

> I am getting the following sub-query error msg,
>
> 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 am getting the msg, just not how to resolve the issue.
> The SR_Master_Data table has the following fields
> ServiceRequestName
> StatusID
> ContactResourceID
> RequestorResourceID
>
> And has a one to one relationship with
> BOSC.BoscReporting.dbo.srm_service_requests table. This table has a one to
> many relationship with BOSC.BoscReporting.dbo.srm_sr_contact_xref
>
> I need the sub queries to return the cont.c_contact_resource_id for the
> current record being updated. I am not sure how to do this.
>
>
>
> UPDATE dbo.SR_Master_Data
> SET     ServiceRequestName = sr.c_description,
>     StatusID = sr.c_status,
>     ContactResourceID = (
>         SELECT cont.c_contact_resource_id
>         FROM BOSC.BoscReporting.dbo.srm_sr_contact_xref cont
>         WHERE cont.n_contact_role_id IN('3')),
>     RequestorResourceID = (
>         SELECT cont.c_contact_resource_id
>         FROM BOSC.BoscReporting.dbo.srm_sr_contact_xref cont
>         WHERE cont.n_contact_role_id IN('1')),
>
> FROM BOSC.BoscReporting.dbo.srm_service_requests sr
>     LEFT OUTER JOIN BOSC.BoscReporting.dbo.srm_sr_contact_xref cont
>     ON sr.c_service_req = cont.c_service_req
> WHERE sr.c_service_req = dbo.SR_Master_Data.ServiceRequestNumber

17 Aug 2006 3:05 PM

Roy Harvey

I don't quite understand what you have. You say that SR_Master_Data
has a one to many relationship to srm_sr_contact_xref (apparently via
srm_sr_contact_xref). This brings up two questions.

1) How do you choose among the "many" rows of srm_sr_contact_xref to
find the right one to use?

2) A bigger potential problem is with the FROM clause of the UPDATE.
Standard SQL does not provide for a FROM clause in UPDATE, this is a
SQL Server extension. It works, but there is a problem when there is
a one-to-many relationship between the table being updated and what it
is being joined to with the FROM. WHICH row from the "many" end of
the relationship is to be used for the update? And you have described
such a one-to-man relationship.

The standard advice when posting here is to include table definitions,
including keys, and sample data and desired results. That might help.

Roy Harvey
Beacon Falls, CT

On Thu, 17 Aug 2006 07:32:02 -0700, Kevin
<Ke***@discussions.microsoft.com> wrote:

Show quote

>I am getting the following sub-query error msg,
>
>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 am getting the msg, just not how to resolve the issue.
>The SR_Master_Data table has the following fields
>ServiceRequestName
>StatusID
>ContactResourceID
>RequestorResourceID
>
>And has a one to one relationship with
>BOSC.BoscReporting.dbo.srm_service_requests table. This table has a one to
>many relationship with BOSC.BoscReporting.dbo.srm_sr_contact_xref
>
>I need the sub queries to return the cont.c_contact_resource_id for the
>current record being updated. I am not sure how to do this.
>
>
>
>UPDATE dbo.SR_Master_Data
>SET     ServiceRequestName = sr.c_description,
>    StatusID = sr.c_status,
>    ContactResourceID = (
>        SELECT cont.c_contact_resource_id
>        FROM BOSC.BoscReporting.dbo.srm_sr_contact_xref cont
>        WHERE cont.n_contact_role_id IN('3')),
>    RequestorResourceID = (
>        SELECT cont.c_contact_resource_id
>        FROM BOSC.BoscReporting.dbo.srm_sr_contact_xref cont
>        WHERE cont.n_contact_role_id IN('1')),
>
>FROM BOSC.BoscReporting.dbo.srm_service_requests sr
>    LEFT OUTER JOIN BOSC.BoscReporting.dbo.srm_sr_contact_xref cont
>    ON sr.c_service_req = cont.c_service_req
>WHERE sr.c_service_req = dbo.SR_Master_Data.ServiceRequestNumber