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_ea...@gmx.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.h.na...@gmail.com ICQ: 499797758 Skype: nagmat84 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
