Re: Data Warehouse Reevaluation - MySQL vs Postgres --

Hi, Mischa,

On Tue, 14 Sep 2004 22:58:20 GMT
Mischa Sandberg <ischamay(dot)andbergsay(at)activestateway(dot)com> wrote:

> Googling 'upsert' (an Oraclism, I believe) will get you hits on Oracle
> and DB2's implementation of MERGE, which does what AMOUNTS to what is
> described below (one mass UPDATE...FROM, one mass INSERT...WHERE NOT
> EXISTS).
>
> No, you shouldn't iterate row-by-row through the temp table.
> Whenever possible, try to do updates in one single (mass) operation.
> Doing it that way gives the optimizer the best chance at amortizing
> fixed costs, and batching operations.

But when every updated row has a different value for the column(s) to be
updated, then I still have to use one update statement per row, which I
expect to be faster when done via a stored procedure than having the
whole client-server roundtrip including parsing every time. Or did I
miss some nice SQL statement?

Have a nice day,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios(at)logi-track(dot)com | www.logi-track.com