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
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 |