Re: select ... for update

> > Hi,
>
> How can I use select ... for update to update limit to update what I
> select??

First thing - the SELECT FOR UPDATE is not merge of SELECT and UPDATE
but transaction option. The PostgreSQL use row-locking for UPDATEed rows.
Standard SELECT ignore this lock, but SELECT FOR UPDATE wait until
*other* transaction with UPDATE will commited.

> somewhat like:
> select url,id from mytable for update order by priority,id limit 5;
^^^^^^^^^^^^^^^^^^^
see the SELECT's syntax, ORDER BY must be before FOR UPDATE.

> I want update the id in above return like:
> update mytable set allocatedto='whatever' where id in above return set.

Can't you in UPDATE's WHERE define some 'id' as in above SELECT?

An example (via subselect):

UPDATE mytable SET allocatedto='whatever'
WHERE id IN (
SELECT id FROM mytable ORDER BY priority,id LIMIT 5
);

But it not will too much fast... better is define relevant 'id'
inside UPDATE's WHERE without sub-select, but if you need define it via
ORDER+LIMIT it's impossible.

Karel