Update statement with select Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update statement with select Query

Author

Topic

sathyarangaraj
Starting Member

11 Posts

Posted - 2012-10-14 : 12:47:46

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

52326 Posts

Posted - 2012-10-15 : 10:52:34

[code]
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]
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources