Update Table with a “Select query” with a where clause

Transact-SQL
1

Hi,
I would like to update the #RunningTotal table, field RunningTotal with the result of the select below and the field total
Many thanks,
Pepe

CREATE TABLE #RunningTotal
      (CL int, fecha date, Entry int ,RunningTotal int)

INSERT INTO #RunningTotal (cl ,fecha ,Entry,RunningTotal)VALUES(1,'2015-01-03',100,NULL)
INSERT INTO #RunningTotal (cl ,fecha ,Entry,RunningTotal)VALUES(1,'2015-02-03',200,NULL)
INSERT INTO #RunningTotal (cl ,fecha ,Entry,RunningTotal)VALUES(1,'2015-05-03',300,NULL)
INSERT INTO #RunningTotal (cl ,fecha ,Entry,RunningTotal)VALUES(1,'2015-07-13',400,NULL)
INSERT INTO #RunningTotal (cl ,fecha ,Entry,RunningTotal)VALUES(1,'2015-01-03',-500,NULL)
INSERT INTO #RunningTotal (cl ,fecha ,Entry,RunningTotal)VALUES(1,'2014-08-03',100,NULL)


select cl, entry, fecha, sum(entry) OVER (order by fecha) as Total 
from #RunningTotal 
where cl=1
order by fecha asc

drop table #RunningTotal
2

You posted the question to SQL 2008R2, yet you are using a feature that is available only in SQL 2012 or later. Assuming you are on SQL 2012 or later, to update the RunningTotal column with the result of your calculation,

;with cte as
(
	select RunningTotal, sum(entry) OVER (order by fecha) as Total 
	from #RunningTotal
)
update cte set RunningTotal = Total;
2 Likes
3

Thank you James. It is indeed 2012

4

Just for clarification :slight_smile: I have changed the TAG on this thread to SQL2012.