Re: UPDATE w/ subselect doing locking

On Thu, 15 Nov 2001, David Ford wrote:

> Yes, I know that works. I want to figure out how to use SELECT ... FOR
> UPDATE with UPDATE because I will have a need to lock these certain rows
> and do a bit more than just 'count'.
>
> >>bluelist=> UPDATE spamcontrol SET count=foo.count+1 FROM (SELECT count
> >>FROM spamcontrol WHERE regex='setup fees' FOR UPDATE) as foo;

In general the problem you're running into in your query is that you're
not specifying which values in foo go with which values in spamcontrol.
You're not limiting the update to only the rows that have 'setup fees'
you're updating all rows with a value from the row that has 'setup fees'.