MySQL Forums
Forum List  »  Stored Procedures

row_number in 5.7.18-log
Posted by: Consultant Fifi
Date: October 25, 2017 04:29PM

Hi, This is my stored procedure which was working fine in 5.6 but after we upgrated to 5.7.18-log it isn't performing as before.
What happens is we add items in a list (tablelist ). If we add it (item_id) between line 3 and 4 (pos)then it's postion becomes 4 and all the rest below in the list, their position get bumped forward accordingly. It was working fine but now even if we select any place in the list, it gets added to the end of list.
UPDATE tablelist AS A
INNER JOIN (
SELECT l.id,
l.pos,
@curRow := @curRow + 1 AS row_number
FROM tablelist l
JOIN (SELECT @curRow := 0) r
WHERE l.item_id = iItemID
ORDER BY l.pos, l.id DESC ) AS B
ON A.id = B.id
SET A.pos = lpad(B.row_number, 4, '0')
WHERE A.item_id = iItemID;
SET @NewLineNo = (
SELECT pos
FROM tablelist
WHERE item_id = iItemID
);

Please can you help me to get this sorted.

Options: ReplyQuote


Subject
Views
Written By
Posted
row_number in 5.7.18-log
4166
October 25, 2017 04:29PM
732
October 25, 2017 06:52PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.