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