[Top] [All Lists]

Re: reorder values

Subject: Re: reorder values
From: "draves"
Date: Sun, 12 Aug 2007 21:41:08 +0000 UTC
Newsgroups: macromedia.coldfusion.cfml_general_discussion
If you were really replacing a row then you could just do and update of row 2.

 I think however you are wanting to make the old 4 the new 5, the old 3 the new 
4 and the old 2 the new 3.  If so you do an update first for the 'received' 
value greater or equal to 2 and set the receive value to the receive value plus 
one.  You would then insert the new row 2.  Around both these transactions you 
would need a cftransaction so if they fail they rollback together to keep 
things in sync.

 If more than one person uses this and the number records is high then this is 
a bad idea.  If the number of records gets large it could take a while and 
other transactions could be started on a different section of the range 
simultaneously which could cause a lot of lock contention in the database.  You 
would have to be sure that the database doesn't allow to rows with the same 
'received' value at the same time so one transaction might error and roll back 
and the other continue so you don't possibly get 2 rows with the same value.

<Prev in Thread] Current Thread [Next in Thread>