Re: Reuse temporary calculation results in an SQL update query [SOLVDED]

Hello,

thank you. The "WITH" clause did the trick. I did not even know that such a thing exists. But as it turns out it makes the statement more readable and elegant but not faster.

The reason for the latter is that both the CTE and the UPDATE statement have the same "FROM ... WHERE ..." part, because the tempory calculation needs some input values from the same table. Hence the table is looked up twice instead once.

Matthias

Am Samstag 29 September 2012, 17:13:18 schrieb Andreas Kretschmer:
>
> Thomas Kellerer <spam_eater(at)gmx(dot)net> hat am 29. September 2012 um 16:13
> geschrieben:
> > Matthias Nagel wrote on 29.09.2012 12:49:
> > > Hello,
> > >
> > > is there any way how one can store the result of a time-consuming
> > > calculation if this result is needed more
> > >than once in an SQL update query? This solution might be PostgreSQL specific
> > >and not standard SQL compliant.
> > > Here is an example of what I want:
> > >
> > > UPDATE table1 SET
> > > StartTime = 'time consuming calculation 1',
> > > StopTime = 'time consuming calculation 2',
> > > Duration = 'time consuming calculation 2' - 'time consuming calculation
> > > 1'
> > > WHERE foo;
> > >
> > > It would be nice, if I could use the "new" start and stop time to calculate
> > > the duration time.
> > >First of all it would make the SQL statement faster and secondly much more
> > >cleaner and easily to understand.
> >
> >
> > Something like:
> >
> > with my_calc as (
> > select pk,
> > time_consuming_calculation_1 as calc1,
> > time_consuming_calculation_2 as calc2
> > from foo
> > )
> > update foo
> > set startTime = my_calc.calc1,
> > stopTime = my_calc.calc2,
> > duration = my_calc.calc2 - calc1
> > where foo.pk = my_calc.pk;
> >
> > http://www.postgresql.org/docs/current/static/queries-with.html#QUERIES-WITH-MODIFYING
>
>
>
> Yeah, with a WITH - CTE, cool ;-)
>
> Andreas
>
>
>
----------------------------------------------------------------------
Matthias Nagel
Willy-Andreas-Allee 1, Zimmer 506
76131 Karlsruhe

Telefon: +49-721-8695-1506
Mobil: +49-151-15998774
e-Mail: matthias(dot)h(dot)nagel(at)gmail(dot)com
ICQ: 499797758
Skype: nagmat84