"Recursive" Update query

Question

Post reply

"Recursive" Update query

SQLTreeo

SSC Enthusiast

Points: 177
More actions
October 7, 2011 at 2:39 am

#249022

Hi,
I have two tables:
-- holding some values for dates
CREATE TABLE vals (dt DATETIME, val INT)
-- holding differences which must be applied to values in Vals table starting by dt date.
CREATE TABLE diffs (dt DATETIME, diff INT)
Content of the tables look following:
Vals table:
1.1.20122
2.1.20124
3.1.20125
4.1.20126
5.1.20127
6.1.20128
7.1.20125
8.1.20123
Diffs table:
4.1.20120,1
8.1.20120,5
I need to update Vals without recursion or cursor or loops to contain following data:
New content of Val table
1.1.20122
2.1.20124
3.1.20125
4.1.20126,1original value + difference from 4.1.2012 (0,1)
5.1.20127,1original value + difference from 4.1.2012
6.1.20128,1original value + difference from 4.1.2012
7.1.20125,1original value + difference from 4.1.2012
8.1.20123,6original value + difference from 4.1.2012 + difference from 8.1.2012 (0,5)
Do you have any idea how to do it without cursors or looping?
Thanks
__________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

something like this should work, it may need some twekaing for large data sets.

CREATE Table #vals (dt DateTime, val int)

Create table #diff (dt DateTime, diff Int)

Insert into #vals

VALUES ('1-JAN-2012', 2)

Insert into #vals

VALUES('2-JAN-2012', 4)

Insert into #vals

VALUES('3-JAN-2012', 5)

Insert into #vals

VALUES('4-JAN-2012', 6)

Insert into #vals

VALUES('5-JAN-2012', 7)

Insert into #vals

VALUES('6-JAN-2012', 8)

Insert into #vals

VALUES('7-JAN-2012', 5)

Insert into #vals

VALUES('8-JAN-2012', 3)

Insert into #diff

VALUES('4-JAN-2012', 1)

Insert into #diff

VALUES('8-JAN-2012', 5)

Select

*

,val + isNull((Select SUM(diff)

From #diff d1

Where d1.dt<=v.dt),0)

From

#vals v

LEFT JOIN #diff d on v.dt=d.dt

Update v

Set

val=val + isNull((Select SUM(diff)

From #diff d1

Where d1.dt<=v.dt),0)

From

#vals v

LEFT JOIN #diff d on v.dt=d.dt

Select * from #vals

drop table #vals

drop table #diff

PS : The selects before and after the update are just to show its working as expected.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

SQLTreeo SSC Enthusiast Points: 177 More actions · Answer 2

Thanks very much!

I wrote exactly same query in the meantime. Only thing I would change for metioned large datasets is adding WHERE clause to last update which filters only rows which have some difference in Diff table. It's just because that correlated subquery in SET will be probably expensive for large sets.

Thanks again.

__________________________________________________________________________________________SQLTreeo.com - My Blog and Free SSMS Productivity Add-In

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 3

I agree, though I dont think you can do that as its not just the rows that have values against them, you have to sum up all the previous ones.

Edit : Code removed wouldnt have worked.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Performance Guard (Shehap) Ten Centuries Points: 1297 More actions · Answer 4

For such types of query , it could be proceeded like below ..

However if the DIffs table contain quite much data entity ,then just we would dispense Temp varialbles below and supersed them by temp table but using Create temptable command not delcare temptable commands to get advantage of temp clustered / nonclustered indexes as well for no longer usage of any Loops or Cursosrs ...etc for the optimal performance hoped for such business logics

declare @diff1 int

declare @diff2 int

select @diff1 = DIFF from diffs where dt ='4.1.2012'

select @diff2 = DIFF from diffs where dt ='8.1.2012'

update vals set val = case when vals.dt>'3.1.2012' and vals.dt<'8.1.2012' then newvalue1 when vals.dt='8.1.2012' then newvalue2 else orgval end from

(select (val+@diff1) as newvalue1 ,( val+@diff1+@diff2) as newvalue2, val as orgval, dt from vals ) temp inner join vals on temp.dt=vals.dt

Please let me know your input

[font="Arial Black"]Performance Guard[/font]

[font="Courier New"]Just ask me to get 0 sec for all queries[/font]

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 5

Performace Guard (Shehap) (10/7/2011)
For such types of query , it could be proceeded like below ..
However if the DIffs table contain quite much data entity ,then just we would dispense Temp varialbles below and supersed them by temp table but using Create temptable command not delcare temptable commands to get advantage of temp clustered / nonclustered indexes as well for no longer usage of any Loops or Cursosrs ...etc for the optimal performance hoped for such business logics
declare @diff1 int
declare @diff2 int
select @diff1 = DIFF from diffs where dt ='4.1.2012'
select @diff2 = DIFF from diffs where dt ='8.1.2012'
update vals set val = case when vals.dt>'3.1.2012' and vals.dt<'8.1.2012' then newvalue1 when vals.dt='8.1.2012' then newvalue2 else orgval end from
(select (val+@diff1) as newvalue1 ,( val+@diff1+@diff2) as newvalue2, val as orgval, dt from vals ) temp inner join vals on temp.dt=vals.dt
Please let me know your input

I'm not sure what your Temp table would look like, or if it would be any different to the Diffs table.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Performance Guard (Shehap) Ten Centuries Points: 1297 More actions · Answer 6

It could be like Diffs so no need to create that temp table like our current case

or subset of Diffs according if it contains much data entity there.

This is just to cover more generic view point about such business cases.

[font="Arial Black"]Performance Guard[/font]

[font="Courier New"]Just ask me to get 0 sec for all queries[/font]

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 7

Any chance you could provide the code for the temp table method you're suggestion as I'm always looking for alternatives to existing methods.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Jakub Dvorak (10/7/2011)
...I need to update Vals without recursion or cursor or loops...

Why not? This is a running totals update. The fastest way to do it is a quirky update, the second fastest is a recursive CTE. The method you're exploring in more detail is a triangular join which will grind your server into the ground on larger data sets. A cursor-driven method would almost certainly be much faster.

Tell us a little more about the problem. How many rows in the two tables? Do you really need to write back to the vals table?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Jakub Dvorak (10/7/2011)
Thanks very much!
I wrote exactly same query in the meantime. Only thing I would change for metioned large datasets is adding WHERE clause to last update which filters only rows which have some difference in Diff table. It's just because that correlated subquery in SET will be probably expensive for large sets.
Thanks again.

Yes, it can be quite expensive. See the following article for why...

http://www.sqlservercentral.com/articles/T-SQL/61539/

I have to disagree with some of my colleagues... recursive CTE's are also quite expensive and can be just as bad or worse than a Cursor and While Loop or a Temp Table and Loop.

How many rows are we talking about this possibly growing to in the next 2 years in each table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)