[thelist] Interesting SQL Puzzle

[Maximillian Schwanekamp]

Frank wrote:
> Joshua wrote:
>> UPDATE table
>> SET rank = rank + 1
>> WHERE rank >= 33
> 
> 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. 

What RDBMS are you using?  Is the "rank" column your primary key or at 
least indexed for unique values?  That would one step in enforcing that 
you do not get duplicate rank values.  Joshua's suggestion is AFAIK the 
most efficient - one query for to "nudge" the records to make a gap, and 
then one more query to insert a new record into the gap.  Taking your 
example of adding a record to a table consisting of 100 records ranked 
1-100, you would do:
/* Joshua's query first */
UDPATE table
SET rank = rank + 1
WHERE rank >= '33';
/* Then insert the record in the sequence gap */
INSERT INTO table
  (id, rank)
VALUES
  (NULL, '33');

This is assuming table.id is an auto-numbered column which is the 
primary key and that rank is an arbitrary value that you're mainly using 
for a user-defined ordering scheme (common in web apps).  You could even 
go so far as to lock the table if you want to be sure that it does not 
get corrupted.  If you're using an RDBMS that supports stored 
procedures, that would be easier yet.

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

AFAIK 500 records is still a pretty trivial table, and even lightweight 
RDBMSs can handle a full-table update without undo resource consumption. 
  As Joshua mentioned, if this is a web app where most queries are 
SELECTs, occasionally updating the rank value for all 64, 100 or 500 or 
whatever records is nothing to worry about.

-- 
Max Schwanekamp
http://www.neptunewebworks.com/