Re: How to update multiple rows atomically

Marshall wrote:
> Here's what I came up with later:
>
> UPDATE T t1, T t2 set t1.owner = 1, t2.owner = 1 where
> t1.owner = 0 and t2.owner = 0 and t1.id = 2 and t2.id = 3;

I find that solution unsatisfying as it is not general. What if there were 102 rows you wanted to update rather than just 2? Your first attempt looked OK, though apparently not for mySQL. Here is another:

update T set owner=1
where <condition>
and not exists
( select null from T
where <condition>
and owner != 0);

Now you don't even need to know in advance how many rows you want to update - e.g. <condition> may be "type = 'WIDGET'". Received on Sat Jul 29 2006 - 16:13:02 CEST