List:General Discussion« Previous MessageNext Message »
From:Mogens Melander Date:July 14 2014 7:54pm
Subject:Re: Stored Procedure help
View as plain text  
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. <yoku0825@stripped> 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 <donw@stripped>:
>>>
>>>> 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
>>>> donw@stripped
>>>> 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.

Thread
Stored Procedure helpDon Wieland14 Jul 2014
  • Re: Stored Procedure helpkitlenv14 Jul 2014
    • Re: Stored Procedure helpMichael Dykman14 Jul 2014
  • Re: Stored Procedure helpyoku ts.14 Jul 2014
    • Re: Stored Procedure helpKeith Murphy14 Jul 2014
      • Re: Stored Procedure helpAnders Karlsson14 Jul 2014
        • Re: Stored Procedure helpMogens Melander14 Jul 2014