UPDATE statement within CTE tables

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 within CTE tables

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-09-24 : 09:47:56

Hi, I have created the following CTE tables but would like to update
AOGM_rev within cte1
with AOTAOT within cte2

Many thanks in advance

with cte1 as
(
select TreeYear,BusinessUnitID,Year,PeriodID,ICTypeKey,AOGM_REV
from View_A_AOGM where Year = '2010' and BusinessUnitID = '01130' and YM = '2010-12'
),cte2 as
(
select year,businessunitid,ictypekey,lk,sum(aot) as AOTAOT from View_A_AOT where Year = '2010' and BusinessUnitID = '01130' and TreeYear = '2011'
and lk = '20' group by year,businessunitid,lk,ICTypeKey
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-24 : 12:07:18

where do you want to update the values to?

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

tariq2
Posting Yak Master

125 Posts

Posted - 2011-09-25 : 14:07:00

I want to update the values of
AOGM_rev within cte1

wit teh values from
AOTAOT within cte2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 00:28:04

[code]
with cte1 as
(
select TreeYear,BusinessUnitID,Year,PeriodID,ICTypeKey,AOGM_REV
from View_A_AOGM where Year = '2010' and BusinessUnitID = '01130' and YM = '2010-12'
),cte2 as
(
select year,businessunitid,ictypekey,lk,sum(aot) as AOTAOT from View_A_AOT where Year = '2010' and BusinessUnitID = '01130' and TreeYear = '2011'
and lk = '20' group by year,businessunitid,lk,ICTypeKey
)

UPDATE c1
STE c1.AOGM_REV=c2.AOTAOT
FROM CTE1 c1
INNER JOIN CTE2 c2
ON c1.BusinessUnitID = c2.BusinessUnitID
AND c1.ictypekey =c2.ictypekey
AND c1.TreeYear = c2.Year
[/code]

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

tariq2
Posting Yak Master

125 Posts

Posted - 2011-09-26 : 07:33:23

Thank you Visakh,
That worked great :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 07:43:00

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources