Anders, I didn't see that at first, but now. I'd agree. Maybe I should read up on stored procedures.
On Mon, July 14, 2014 16:25, Anders Karlsson wrote: > 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 >>>> > > > -- > > Anders Karlsson, Senior Sales Engineer > SkySQL | t: +46 708-608-121 | Skype: drdatabase > -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
