Highlighted

MySQL in ColdFusion: after delete of a record, update with new sort order

LEGEND ,
Jan 30, 2016

Copy link to clipboard

Copied

Hello, all,

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

For example:

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.

V/r,

^_^

Views

153

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

MySQL in ColdFusion: after delete of a record, update with new sort order

LEGEND ,
Jan 30, 2016

Copy link to clipboard

Copied

Hello, all,

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

For example:

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.

V/r,

^_^

Views

154

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jan 30, 2016 0
Adobe Community Professional ,
Jan 31, 2016

Copy link to clipboard

Copied

Yes, you can do it in SQL. Run the following two commands in sequence:

DROP PROCEDURE IF EXISTS reset_tableA_autoincrement;

DELIMITER $$

CREATE PROCEDURE reset_tableA_autoincrement()

BEGIN

  SET @x := 0 ;

  UPDATE tableA

  SET SortOrder = ( SELECT @x := @x + 1 )

  ORDER BY SortOrder ASC;

END $$

followed by

CALL reset_tableA_autoincrement()

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 31, 2016 0
LEGEND ,
Feb 01, 2016

Copy link to clipboard

Copied

Hi, BKBK‌,

Thank you.  I'll give that a shot as soon as I get home, tonight.  (This is for a side-project I'm working on.)

V/r,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 01, 2016 0