Re: Need to select and update with the same sql statement - Mailing list pgsql-php

On Wed, 13 Nov 2002, David Busby wrote:

> List,
>     I need to do a command like:
>
>     select * from "table" where "id"=54; update "table" set "col"=value
> where "id"=just selected id
>
>     Is that possible?  How would I work that into a StoredProcedure? I'm
> getting a race condition where two+ clients are asking for data but getting
> the same record, (each record is a datachunk for a distributed client).
> This results in each client working on the same data, not good.  Any ideas?
> I'm posting to the php/sql list cause the clients ask for the datachunk via
> SOAP request that is processed via PHP.  Any assistance would be great

It's time for transactions!

You should be able to do this in a transaction:

(pg_exec the SQL code here)
begin;
select * from table where "id"=54;
(assign the id to a var $id here)
update "table" set "col"=$value where "id"=$id;
end;

This should happen in such a way that other users can't see what's
happening until it's done.