[thelist] Interesting SQL Puzzle

Frank lists at frankmarion.com
Sat Aug 20 12:14:18 CDT 2005

At 12:31 PM 2005-08-20, you wrote:
>Assume your update is something like this:
>UPDATE table
>SET rank = rank + 1
>WHERE rank >= 33
>The update will be really fast, even with a large number of records.  Since
>this is a web application, I'm going to jump out on a limb and assume that
>SELECTs far out number the UPDATEs and INSERTs.  If this is true, then even
>if this WAS somewhat slow it wouldn't be a big deal in the big picture.

Yes, this is the easy part, but if we already have have a number 34, this 
operation would leave us with TWO records of rank #34, which is what I'm 
trying to avoid. I was also hoping to avoid looping over each record (from 
33 to the end). I'm not sure that I can avoid the overall update to all the 
records. My concern was that I might end up with 500+ records, and that it 
would consume lots of resources, and that it increases the risk of 
corruption to the database.

> > Seems to me that updating 64 records with a different
> > rank might be rather inefficient, especially if we have a
> > lot of records.
>BTW, 64 records is always 64 records, even IF you have lots of records.  ;-)

<chuckle> Got me.

Frank Marion     lists at frankmarion.com      Keep the signal high.

More information about the thelist mailing list