February 14, 2013 at 8:03 am
I have a need to update a field in a table based on the results of a CTE. The CTE calculates the 20 day moving average. The CTE was developed with the assistance of other forum members and can be viewed in the post, http://www.sqlservercentral.com/Forums/Topic1417876-392-1.aspx.
Now I need to take the results of that CTE and update an underlying table. In the example provided below, I am keeping everything very simple, no calculations, no joins, etc. If you can assist me with the general outline or principle, I will try to apply it to what I need once I understand the basics.
Simplistic example:
The CTE which returns the MovingAverage
with SimpleUpdate as (Select Source, Sink, MovingAverage From TestNode)
select Source,Sink, MovingAverage from SimpleUpdate
The result I would like would look something like:
Update TestNode Set MovingAverage = (MovingAverage Result of CTE where Source and Sink match the record being updated)
What I want to do is update the underlying table, TestNode, with the results. I realize that in my example no calculations are being performed on MovingAverage so the resultant table will be exactly the same as the originating table. I get that. But I do not know how to do an UPDATE using the results of a CTE. If someone could offer an example based on the above CTE I would appreciate it.
February 14, 2013 at 8:17 am
Unless I'm missing something, you would update it the same way you would if it were a physical table:
with SimpleUpdate as (Select Source, Sink, MovingAverage From TestNode)
UPDATE A
SET A.MovingAverage= B.MovingAverageResult
FROM TestNode A
JOIN SimpleUpdate B
ON A.Source = B.Source
AND A.Sink = B.Sink
February 14, 2013 at 8:20 am
I know I am missing something. Your cte is nothing more than a select from a table. Now you want to update the table to have the same value?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 14, 2013 at 8:44 am
Fair enough. Let me add a little complexity. Below is the CTE which calculates the 20 day moving average using a Date lookup table.
with MstrTbl as (Select N.Source, N.Sink, N.TimePoint, D.TPIntDate, D.TPHour, N.Delta From dbo.testnode N
Inner Join dbo.TestDate D
On N.TimePoint = D.TimePoint
)
Select A.Source, A.Sink, A.TimePoint as TPA,
A.TPHour as HourA,
avg(B.Delta) as DeltaB, COUNT(A.Source) as NoOfDaysAvged
from MstrTbl A
Inner Join MstrTbl B
On A.source = B.Source and A.Sink = B.Sink and A.TPHour = B.TPHour and A.TPIntDate >= B.TPIntDate and A.TPIntDate -19 <= B.TPIntDate
Group by A.Source, A.Sink, A.TimePoint,A.TPHour
Assuming the dbo.TestNode table has the the column, MovingAverage, what would the UPDATE statement look like?
The JOIN would be on A.Source, A.Sink, A.TPIntDate and A.Hour Or A.Source, A.Sink,A.TimePoint. Please note that A.TPIndDate and A.Hour are lookups from the TestDate table.
pat
February 14, 2013 at 9:29 am
Here is my best attempt. But it fails miserably.
with MstrTbl as (Select N.Source, N.Sink, N.TimePoint, D.TPIntDate, D.TPHour, N.Delta From dbo.testnode N
Inner Join dbo.TestDate D
On N.TimePoint = D.TimePoint
)
Select A.Source, A.Sink, A.TimePoint as TPA,
A.TPHour as HourA,
avg(B.Delta) as DeltaB, COUNT(A.Source) as NoOfDaysAvged
from MstrTbl A
Inner Join MstrTbl B
On A.source = B.Source and A.Sink = B.Sink and A.TPHour = B.TPHour and A.TPIntDate >= B.TPIntDate and A.TPIntDate -19 <= B.TPIntDate
Group by A.Source, A.Sink, A.TimePoint,A.TPHour
Order by A.Source, A.Sink, A.TimePoint,A.TPHour
Update N Set MovingAverage = A.DeltaB From A
Inner Join dbo.TestNode N
On A.Source = N.Source and A.Sink = N.Sink and A.Timepoint = N.TimePoint
February 14, 2013 at 9:38 am
I am still missing something here. Your cte is just a select statement. Are you doing the calculation in the cte or outside of it? Remember I don't know your project, don't have any data to work with and I can't see your screen.
You can actually update a cte with a simple update statement. From what you have posted I don't quite understand why you even have a cte, it is just a select statement.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 14, 2013 at 10:23 am
Sean,
The Select statement returns a calculated value, the 20 day moving average. I need to update the empty field MovingAverage with the results that are returned.
The detail for creating the tables and sample data are in the other post. However, since it appears this information may make it easier for those reading this post. I have reposted it below. Originally I did not do that because I thought the answer might be simple and I didn't want to complicate the issue. That being said,
The Date table which is used for lookups.
/****** Object: Table [dbo].[TestDate] Script Date: 02/08/2013 13:58:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestDate](
[TimePoint] [datetime] NULL,
[TPYear] [int] NULL,
[TPMth] [int] NULL,
[TPDay] [int] NULL,
[TPHour] [int] NULL,
[TPIntDate] [int] NULL
) ON [PRIMARY]
GO
Insert into TestDate(Timepoint,TPYear,TPMth, TPDay, TPHour, TPIntDate)
select '01/01/2012 05:00:00',2012,1,1,5,1
union
select '01/02/2012 05:00:00',2012,1,2,5,2
union
select '01/03/2012 05:00:00',2012,1,3,5,3
union
select '01/04/2012 05:00:00',2012,1,4,5,4
union
select '01/05/2012 05:00:00',2012,1,5,5,5
union
select '01/06/2012 05:00:00',2012,1,6,5,6
union
select '01/07/2012 05:00:00',2012,1,7,5,7
union
select '01/08/2012 05:00:00',2012,1,8,5,8
union
select '01/09/2012 05:00:00',2012,1,9,5,9
union
select '01/10/2012 05:00:00',2012,1,10,5,10
union
select '01/11/2012 05:00:00',2012,1,11,5,11
union
select '01/12/2012 05:00:00',2012,1,12,5,12
union
select '01/13/2012 05:00:00',2012,1,13,5,13
union
select '01/14/2012 05:00:00',2012,1,14,5,14
union
select '01/15/2012 05:00:00',2012,1,15,5,15
union
select '01/16/2012 05:00:00',2012,1,16,5,16
union
select '01/17/2012 05:00:00',2012,1,17,5,17
union
select '01/18/2012 05:00:00',2012,1,18,5,18
union
select '01/19/2012 05:00:00',2012,1,19,5,19
union
select '01/20/2012 05:00:00',2012,1,20,5,20
union
select '01/21/2012 05:00:00',2012,1,21,5,21
union
select '01/22/2012 05:00:00',2012,1,22,5,22
union
select '01/23/2012 05:00:00',2012,1,23,5,23
union
select '01/24/2012 05:00:00',2012,1,24,5,24
union
select '01/25/2012 05:00:00',2012,1,25,5,25
union
select '01/26/2012 05:00:00',2012,1,26,5,26
union
select '01/27/2012 05:00:00',2012,1,27,5,27
union
select '01/28/2012 05:00:00',2012,1,28,5,28
union
select '01/29/2012 05:00:00',2012,1,29,5,29
union
select '01/30/2012 05:00:00',2012,1,30,5,30
union
select '01/31/2012 05:00:00',2012,1,31,5,31
union
select '02/01/2012 05:00:00',2012,2,1,5,32
union
select '02/02/2012 05:00:00',2012,2,2,5,33
union
select '02/03/2012 05:00:00',2012,2,3,5,34
union
select '02/04/2012 05:00:00',2012,2,4,5,35
union
select '02/05/2012 05:00:00',2012,2,5,5,36
union
select '02/06/2012 05:00:00',2012,2,6,5,37
union
select '02/07/2012 05:00:00',2012,2,7,5,38
union
select '02/08/2012 05:00:00',2012,2,8,5,39
union
select '02/09/2012 05:00:00',2012,2,9,5,40
union
select '02/10/2012 05:00:00',2012,2,10,5,41
union
select '02/11/2012 05:00:00',2012,2,11,5,42
union
select '02/12/2012 05:00:00',2012,2,12,5,43
The Data table,
/****** Object: Table [dbo].[TestNode] Script Date: 02/08/2013 14:08:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TestNode](
[TimePoint] [datetime] NULL,
[Sink] [varchar](50) NULL,
[Source] [varchar](50) NULL,
[Delta] [decimal](8, 2) NULL
[MovingAverage] [decimal](8, 2) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Insert into TestNode(Timepoint,Sink, Source, Delta)
select '01/01/2012 05:00:00','PEACH BOTTOM','OVEC',1
union
select '01/02/2012 05:00:00','PEACH BOTTOM','OVEC',1.5
union
select '01/03/2012 05:00:00','PEACH BOTTOM','OVEC',1.7
union
select '01/04/2012 05:00:00','PEACH BOTTOM','OVEC',9
union
select '01/05/2012 05:00:00','PEACH BOTTOM','OVEC',6
union
select '01/06/2012 05:00:00','PEACH BOTTOM','OVEC',-8
union
select '01/07/2012 05:00:00','PEACH BOTTOM','OVEC',5
union
select '01/08/2012 05:00:00','PEACH BOTTOM','OVEC',4
union
select '01/09/2012 05:00:00','PEACH BOTTOM','OVEC',5.1
union
select '01/10/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '01/11/2012 05:00:00','PEACH BOTTOM','OVEC',3
union
select '01/12/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '01/13/2012 05:00:00','PEACH BOTTOM','OVEC',35
union
select '01/14/2012 05:00:00','PEACH BOTTOM','OVEC',-9
union
select '01/15/2012 05:00:00','PEACH BOTTOM','OVEC',3
union
select '01/16/2012 05:00:00','PEACH BOTTOM','OVEC',47
union
select '01/17/2012 05:00:00','PEACH BOTTOM','OVEC',1.6
union
select '01/18/2012 05:00:00','PEACH BOTTOM','OVEC',7.4
union
select '01/19/2012 05:00:00','PEACH BOTTOM','OVEC',5
union
select '01/20/2012 05:00:00','PEACH BOTTOM','OVEC',6.3
union
select '01/21/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '01/22/2012 05:00:00','PEACH BOTTOM','OVEC',8
union
select '01/23/2012 05:00:00','PEACH BOTTOM','OVEC',6.5
union
select '01/24/2012 05:00:00','PEACH BOTTOM','OVEC',-7
union
select '01/25/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '01/26/2012 05:00:00','PEACH BOTTOM','OVEC',6.2
union
select '01/27/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '01/28/2012 05:00:00','PEACH BOTTOM','OVEC',1
union
select '01/29/2012 05:00:00','PEACH BOTTOM','OVEC',-8
union
select '01/30/2012 05:00:00','PEACH BOTTOM','OVEC',-3
union
select '01/31/2012 05:00:00','PEACH BOTTOM','OVEC',7
union
select '02/01/2012 05:00:00','PEACH BOTTOM','OVEC',10
union
select '02/02/2012 05:00:00','PEACH BOTTOM','OVEC',9
union
select '02/03/2012 05:00:00','PEACH BOTTOM','OVEC',6
union
select '02/04/2012 05:00:00','PEACH BOTTOM','OVEC',-9
union
select '02/05/2012 05:00:00','PEACH BOTTOM','OVEC',-74
union
select '02/06/2012 05:00:00','PEACH BOTTOM','OVEC',6
union
select '02/07/2012 05:00:00','PEACH BOTTOM','OVEC',3
union
select '02/08/2012 05:00:00','PEACH BOTTOM','OVEC',34
union
select '02/09/2012 05:00:00','PEACH BOTTOM','OVEC',-54
union
select '02/10/2012 05:00:00','PEACH BOTTOM','OVEC',-41
union
select '02/11/2012 05:00:00','PEACH BOTTOM','OVEC',-35
union
select '02/12/2012 05:00:00','PEACH BOTTOM','OVEC',100
Union
select '01/01/2012 05:00:00','PEACH BOTTOM','ELMARA',1
union
select '01/02/2012 05:00:00','PEACH BOTTOM','ELMARA',1.5
union
select '01/03/2012 05:00:00','PEACH BOTTOM','ELMARA',1.7
union
select '01/04/2012 05:00:00','PEACH BOTTOM','ELMARA',9
union
select '01/05/2012 05:00:00','PEACH BOTTOM','ELMARA',6
union
select '01/06/2012 05:00:00','PEACH BOTTOM','ELMARA',-8
union
select '01/07/2012 05:00:00','PEACH BOTTOM','ELMARA',5
union
select '01/08/2012 05:00:00','PEACH BOTTOM','ELMARA',4
union
select '01/09/2012 05:00:00','PEACH BOTTOM','ELMARA',5.1
union
select '01/10/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '01/11/2012 05:00:00','PEACH BOTTOM','ELMARA',3
union
select '01/12/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '01/13/2012 05:00:00','PEACH BOTTOM','ELMARA',35
union
select '01/14/2012 05:00:00','PEACH BOTTOM','ELMARA',-9
union
select '01/15/2012 05:00:00','PEACH BOTTOM','ELMARA',3
union
select '01/16/2012 05:00:00','PEACH BOTTOM','ELMARA',47
union
select '01/17/2012 05:00:00','PEACH BOTTOM','ELMARA',1.6
union
select '01/18/2012 05:00:00','PEACH BOTTOM','ELMARA',7.4
union
select '01/19/2012 05:00:00','PEACH BOTTOM','ELMARA',5
union
select '01/20/2012 05:00:00','PEACH BOTTOM','ELMARA',6.3
union
select '01/21/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '01/22/2012 05:00:00','PEACH BOTTOM','ELMARA',8
union
select '01/23/2012 05:00:00','PEACH BOTTOM','ELMARA',6.5
union
select '01/24/2012 05:00:00','PEACH BOTTOM','ELMARA',-7
union
select '01/25/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '01/26/2012 05:00:00','PEACH BOTTOM','ELMARA',6.2
union
select '01/27/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '01/28/2012 05:00:00','PEACH BOTTOM','ELMARA',1
union
select '01/29/2012 05:00:00','PEACH BOTTOM','ELMARA',-8
union
select '01/30/2012 05:00:00','PEACH BOTTOM','ELMARA',-3
union
select '01/31/2012 05:00:00','PEACH BOTTOM','ELMARA',7
union
select '02/01/2012 05:00:00','PEACH BOTTOM','ELMARA',10
union
select '02/02/2012 05:00:00','PEACH BOTTOM','ELMARA',9
union
select '02/03/2012 05:00:00','PEACH BOTTOM','ELMARA',6
union
select '02/04/2012 05:00:00','PEACH BOTTOM','ELMARA',-9
union
select '02/05/2012 05:00:00','PEACH BOTTOM','ELMARA',-74
union
select '02/06/2012 05:00:00','PEACH BOTTOM','ELMARA',6
union
select '02/07/2012 05:00:00','PEACH BOTTOM','ELMARA',3
union
select '02/08/2012 05:00:00','PEACH BOTTOM','ELMARA',34
union
select '02/09/2012 05:00:00','PEACH BOTTOM','ELMARA',-54
union
select '02/10/2012 05:00:00','PEACH BOTTOM','ELMARA',-41
union
select '02/11/2012 05:00:00','PEACH BOTTOM','ELMARA',-35
union
select '02/12/2012 05:00:00','PEACH BOTTOM','ELMARA',100
If you need anything else, please do not hesitate to ask.
pat
February 14, 2013 at 10:55 am
Got it!!!!!!!!!
with MstrTbl as (Select N.Source, N.Sink, N.TimePoint, D.TPIntDate, D.TPHour, N.Delta From dbo.testnode N
Inner Join dbo.TestDate D
On N.TimePoint = D.TimePoint
)
Update dbo.TestNode
set TestNode.MovingAverage = CteMA.DeltaB
From (
Select A.Source, A.Sink, A.TimePoint as TPA,
A.TPHour as HourA,
avg(B.Delta) as DeltaB, COUNT(A.Source) as NoOfDaysAvged
from MstrTbl A
Inner Join MstrTbl B
On A.source = B.Source and A.Sink = B.Sink and A.TPHour = B.TPHour and A.TPIntDate >= B.TPIntDate and A.TPIntDate -19 <= B.TPIntDate
Group by A.Source, A.Sink, A.TimePoint,A.TPHour
) as CTEMA
where TestNode.Source = CTEMA.Source and TestNode.Sink = CTEMA.Sink and TestNode.TimePoint = CTEMA.TPA
Sometimes just asking for help is helpful.
Thanks,
pat
February 14, 2013 at 12:25 pm
Cool. Thanks for letting us know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply