InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 14:24:22 GMT20http://www.sqlservercentral.com/Forums/Topic1384952-391-1.aspxYes see this query can give you more then one record here . look into this query [code="sql"]select B.ICPfrom [R33HypInfa].[dbo].[DF012_Look_up_hfm] A,[R33HypInfa].[dbo].[DF012_HFM_Staging]BWhere A.Account=B.Account AND A.Icp='VS001'[/code]Thu, 15 Nov 2012 03:01:41 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1384952-391-1.aspx[code="sql"]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');[/code]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?Thu, 15 Nov 2012 01:49:18 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1384952-391-1.aspxAs 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.Wed, 14 Nov 2012 22:40:50 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1384952-391-1.aspxFrom: "Mishra, Abhinash, Vodafone UK, Partner" <abhinash.mishra@vodafone.com>To: Abinash Mishra1/India/IBM@IBMIN, Date: 11/15/2012 08:06 AMSubject: procUSE [R33HypInfa]GOcreate procedure DF012_ValidationAS BEGINupdate [R33HypInfa].[dbo].[DF012_HFM_Staging]set ICP=(select B.ICPfrom [R33HypInfa].[dbo].[DF012_Look_up_hfm] A,[R33HypInfa].[dbo].[DF012_HFM_Staging]BWhere 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]BWhere 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.Custom2from [R33HypInfa].[dbo].[DF012_Look_up_hfm] A,[R33HypInfa].[dbo].[DF012_HFM_Staging]BWhere A.Account=B.Account AND A.Custom2 in(select distinct Base_custom2 from [R33HypInfa].[dbo].[DF012_LKP_COMPARE_C2] whereB.Custom2 in (Select Custom2 from [R33HypInfa].[dbo].[DF012_LKP_COMPARE_C2] )));ENDGOWhen I execute this below error is encounteredError-:Msg 512, Level 16, State 1, Line 1Subquery 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.Wed, 14 Nov 2012 20:03:03 GMTabinash.mishra84