Need help with the below procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Need help with the below procedure

Need help with the below procedure

Rate Topic

Display Mode

Topic Options

Author

Message

abinash.mishra84

Posted Wednesday, November 14, 2012 8:03 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 28, 2013 4:25 PM
Points: 2, Visits: 9

From: "Mishra, Abhinash, Vodafone UK, Partner" <abhinash.mishra@vodafone.com>
To: Abinash Mishra1/India/IBM@IBMIN,
Date: 11/15/2012 08:06 AM
Subject: proc

USE [R33HypInfa]
GO
create procedure DF012_Validation
AS
BEGIN
update [R33HypInfa].[dbo].[DF012_HFM_Staging]
set ICP=(select B.ICP
from [R33HypInfa].[dbo].[DF012_Look_up_hfm] A,[R33HypInfa].[dbo].[DF012_HFM_Staging]B
Where A.Account=B.Account AND A.Icp='VS001');
update [R33HypInfa].[dbo].[DF012_HFM_Staging]
Set Custom1=(select B.Custom1
from [R33HypInfa].[dbo].[DF012_Look_up_hfm] A,[R33HypInfa].[dbo].[DF012_HFM_Staging]B
Where A.Account=B.Account AND A.Custom1 in(select distinct Base_custom1 from [R33HypInfa].[dbo].[DF012_LKP_COMPARE_C1]
where B.Custom1 in (Select Custom1 from [R33HypInfa].[dbo].[DF012_LKP_COMPARE_C1] )));
set Custom2=(select B.Custom2
from [R33HypInfa].[dbo].[DF012_Look_up_hfm] A,[R33HypInfa].[dbo].[DF012_HFM_Staging]B
Where A.Account=B.Account AND A.Custom2 in(select distinct Base_custom2 from [R33HypInfa].[dbo].[DF012_LKP_COMPARE_C2]
where
B.Custom2 in (Select Custom2 from [R33HypInfa].[dbo].[DF012_LKP_COMPARE_C2] )));
END
GO

When I execute this below error is encountered

Error-:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Post #1384952

vinu512

Posted Wednesday, November 14, 2012 10:40 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:37 AM
Points: 1,047, Visits: 1,439

As you can guess from the error....one of your queries is returning more than a single value. But I won't be able to help you much just by looking at the code.....so you could either provide DDL and some sample data which would depict your problem and then perhaps we could correct the code for you.

Otherwise, you can just replace the "=" signs before the sub queries with "IN". But, that is if your data permits that as it could create results that you are not trying to do and might update more rows than actually you intend to update.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden Wink

Post #1384978

Jason-299789

Posted Thursday, November 15, 2012 1:49 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:30 AM
Points: 803, Visits: 2,124

update [R33HypInfa].[dbo].[DF012_HFM_Staging]
set ICP=
        (select B.ICP
            from [R33HypInfa].[dbo].[DF012_Look_up_hfm] A
                        ,[R33HypInfa].[dbo].[DF012_HFM_Staging]B
            Where A.Account=B.Account AND A.Icp='VS001');

the way this is written explains why you have duplicate values, as I'm willing to bet money that the select returns more than one B.ICP value if run independantly of the update.

what is it you are trying to achieve?

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1385004

Bhuvnesh

Posted Thursday, November 15, 2012 3:01 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

Yes see this query can give you more then one record here . look into this query

select B.ICP
from [R33HypInfa].[dbo].[DF012_Look_up_hfm] A,[R33HypInfa].[dbo].[DF012_HFM_Staging]B
Where A.Account=B.Account AND A.Icp='VS001'

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1385027

« Prev Topic | Next Topic »

Permissions