Re: How to update multiple rows atomically

Tony Andrews wrote:
> 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.

Me, too.

> 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'".

I like it!

Marshall Received on Sat Jul 29 2006 - 16:36:15 CEST