Re: support for atomic multi-table updates?

From: "Henshall, Stuart - WCP" <SHenshall(at)westcountrypublications(dot)co(dot)uk>
To: 'Steve Aulenbach' <aulenbac(at)ucar(dot)edu>, pgsql-novice(at)postgresql(dot)org
Subject: Re: support for atomic multi-table updates?
Date: 2002-04-25 08:45:54
Message-ID: E2870D8CE1CCD311BAF50008C71EDE8E01F74861@MAIL_EXCHANGE
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

You can use transactions to ensure that either both happen or neither happen
over an arbitrary number of commands.
eg:
BEGIN;
UPDATE x SET a=1;
UPDATE y SET b=2;
COMMIT;
Neither update will be visible to another transaction until the commit and
if there is a crash before the commit it will rollback on restart.
However in your specific case you should just be able to do something like
this:
UPDATE items SET items.price=(SELECT month.price FROM month WHERE
month.id=item.id);
Hope this helps,
- Stuart
(As a side note this looks like a candidate for normalisation, just joining
like so:
SELECT items.*,month.price FROM items INNER JOIN month ON items.id=month.id;

or
SELECT items.*,month.price FROM items,month WHERE items.id=month.id;
(the back end will try and optimise the table join order for this one))

> From: Steve Aulenbach [mailto:aulenbac(at)ucar(dot)edu]
>
>
> Hi,
>
> Does postgreSQL support atomic multi-table updates? Something like:
>
> update items,month set items.price=month.price where
> items.id=month.id;
>
> We are considering postgreSQL for a project where we would like to be
> able to do this.
>
> Thanks,
> Steve Aulenbach

Browse pgsql-novice by date

  From Date Subject
Next Message Klaudia Walter 2002-04-25 13:39:39 Problems with \copy
Previous Message Warwick Hunter 2002-04-24 23:58:36 Re: Multithreading