The order makes quite a big difference, actually. In this case it
ensures that the ordering of the values in the sort_id column is
maintained, even though the numbers are different.
Say this is your data (I have ignored the category thingy for now):
SELECT id, sort_id FROM documents;
+------+---------+
| id | sort_id |
+------+---------+
| 1 | 12 |
| 2 | 13 |
| 3 | 11 |
+------+---------+
Now if I run this the update without the order by:
UPDATE documents SET sort_id = (@a := @a + 1) WHERE
document_category = category;
The result will be:
SELECT id, sort_id FROM documents;
+------+---------+
| id | sort_id |
+------+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+---------+
Whereas with the order by
UPDATE documents SET sort_id = (@a := @a + 1) WHERE
document_category = category ORDER BY sort_id;
the result would be:
+------+---------+
| id | sort_id |
+------+---------+
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
+------+---------+
/Karlsson
Keith Murphy skrev 2014-07-14 15:31:
I would second what m. dykman says. There is no reason I can think of that
you would even be doing the order by clause.
keith
On Sun, Jul 13, 2014 at 11:16 PM, yoku ts. <yoku0...@gmail.com> wrote:
Would you try this?
CREATE PROCEDURE `reset_sortid` (IN category INT(11))
BEGIN
SET @a = 0;
UPDATE
documents SET sort_id = (@a := @a + 1)
WHERE
document_category = category
ORDER BY
sort_id;
END
//
2014-07-14 11:42 GMT+09:00 Don Wieland <d...@pointmade.net>:
I am trying to create this stored procedure, but can't understand why my
editor is chocking on it. Little help please:
DELIMITER //
CREATE PROCEDURE `reset_sortid` (IN category INT(11))
BEGIN
DECLARE a INT;
SET a = 0;
UPDATE
documents SET sort_id = (a := a + 1)
WHERE
document_category = category
ORDER BY
sort_id;
END
//
Don Wieland
d...@pointmade.net
http://www.pointmade.net
https://www.facebook.com/pointmade.band
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
--
Anders Karlsson, Senior Sales Engineer
SkySQL | t: +46 708-608-121 | Skype: drdatabase
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
