SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update statement with select Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sathyarangaraj
Starting Member

India
11 Posts

Posted - 10/14/2012 :  12:47:46  Show Profile  Reply with Quote
Hi All,

I need to update the non_verified parter table with sellout% as per the Partner id for the last 12 running months. When i run the query with the "Groupby [partner id]" i am getting error "Subquery is returning more than one value" and with the Groupby [Partner id], same data is getting updated for all the rows.

UPDATE dbo.tbl_Non_Verified_Partner
SET [HW_PCLW]=(SELECT SUM(A.[Sellout Est Net CLC]) /
(select SUM([Sellout Est Net CLC]) from dbo.tbl_FlashProData WHERE Month>(SELECT CAST(CONVERT(VARCHAR(6), DATEADD(MONTH, DATEDIFF(MONTH, 0, SYSDATETIME()) - 13, 0), 112) AS INT)))
from dbo.tbl_FlashProData A INNER JOIN dbo.tbl_Non_Verified_Partner B ON B.[SYS_ID]=A.[HQ Partner ID] inner join dbo.tbl_PLMapping C ON A.[Current Product Line] = C.PL AND C.Category='HW_PCLW' AND a.Month>(SELECT CAST(CONVERT(VARCHAR(6), DATEADD(MONTH, DATEDIFF(MONTH, 0, SYSDATETIME()) - 13, 0), 112) AS INT)) GROUP BY A.[HQ Partner ID])


Thanks,
Sathya

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/15/2012 :  10:52:34  Show Profile  Reply with Quote

UPDATE B
SET B.[HW_PCLW]= t.HW_PCLW
FROM
(SELECT A.[HQ Partner ID],
SUM(A.[Sellout Est Net CLC]) / 
(select SUM([Sellout Est Net CLC]) 
from dbo.tbl_FlashProData 
WHERE Month>(SELECT CAST(CONVERT(VARCHAR(6), DATEADD(MONTH, DATEDIFF(MONTH, 0, SYSDATETIME()) - 13, 0), 112) AS INT)
)
) AS [HW_PCLW]
from dbo.tbl_FlashProData A  
inner join dbo.tbl_PLMapping C 
ON A.[Current Product Line] = C.PL AND C.Category='HW_PCLW' 
AND a.Month>(SELECT CAST(CONVERT(VARCHAR(6), DATEADD(MONTH, DATEDIFF(MONTH, 0, SYSDATETIME()) - 13, 0), 112) AS INT)) 
GROUP BY A.[HQ Partner ID]
) t
INNER JOIN dbo.tbl_Non_Verified_Partner B 
ON B.[SYS_ID]=A.[HQ Partner ID]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000