[thelist] Interesting SQL Puzzle

[Joshua Olson]

> -----Original Message-----
> From: Frank
> Sent: Saturday, August 20, 2005 1:14 PM
> 
> 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
 
> 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. 

Frank,

Assuming that you know the PK (id field) of the record you just entered,
then you can modify the query as such:

UPDATE table
SET rank = rank + 1
WHERE rank >= 33
  AND id <> #theoneIjustentered#

Easy nuff?

<><><><><><><><><><>
Joshua L. Olson
WAE Tech Inc.
http://www.waetech.com/
Phone: 706.210.0168 
Fax: 413.812.4864

Monitor bandwidth usage on IIS6 in real-time:
http://www.waetech.com/services/iisbm/