I'm trying to automatically update a sort order column after a record has been deleted, so that there are no gaps (yes, I know it still will work with gaps.. my OCD brain cringes at the thought.)
UUID |Headline |Article |Active | SortOrder =================================================================================== ... This is a headline This is article text 1 1 ... Another headline Different article text 1 2 ... Still yet another Still different text 1 3 ... These are boring Boring article text 1 4
I'm trying to set it so that if the second article (SortOrder is 2) is deleted, the third SortOrder becomes 2, the fourth SortOrder becomes 3.
I've tried (found at this link - apparently it's PHP, not MySQL or ColdFusion):
SET @x = 0; UPDATE tableA SET SortOrder = (@x:=@x+1) ORDER BY SortOrder;
But this just gives me a generic "check your MySQL manual for syntax; error where 'Set SortOrder = (@x:=@x+1)' " message.
Is there a way to do this in the SQL without having to use one query to get the SortOrder and another to reset based upon a CF variable? That's what I've got in place, now, and I hate it.
Yes, you can do it in SQL. Run the following two commands in sequence:
DROP PROCEDURE IF EXISTS reset_tableA_autoincrement;
CREATE PROCEDURE reset_tableA_autoincrement()
SET @x := 0 ;
SET SortOrder = ( SELECT @x := @x + 1 )
ORDER BY SortOrder ASC;