How to update sequence in MySQL

Jassim Rahma

Hi, I have order_tier database and I am adding tiers by checking what's the max number then add 1 to it. Now I have a problem when deleting because if the order has 4 tiers then it should have from 1 to 4, if the user deleted tier 4 then it will be 1 to 3, if the user deleted tier 2 then tier 3 should be 2 and 4 should be 3.. By this I mean it should have always a sequence without a gap in the middle and it should move from the bottom so if user deleted tier 6 of 7 tier's order then tier 7 will be 6, etc. How can I implement this in using PL/SQL in MySQL?

DECLARE param\_last\_tier INT;

IF EXISTS (SELECT tier\_number FROM order\_tiers WHERE order\_id = param\_order\_id) THEN
BEGIN
	SET param\_last\_tier = (SELECT MAX(tier\_number) FROM order\_tiers WHERE order\_id = param\_order\_id) + 1;
END;
ELSE
BEGIN
	SET param\_last\_tier = 1;
END;
END IF;

INSERT INTO order\_tiers (order\_id, tier\_type, tier\_number, created\_user)
VALUES (param\_order\_id, param\_tier\_type, param\_last\_tier, param\_created\_user);

Thanks, Jassim[^]

Technology News @ www.JassimRahma.com

CHill60

I think what you are looking for is a way to emulate the sql row_number function within MySQL (or do you mean Oracle PL/SQL?) This article[^] shows a method which I've used successfully in the past ... example posted here in case the link dies

SET @row_number = 0;

SELECT
(@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
employees
LIMIT 5;

As an aside, using Max() to determine the "next" number when inserting rows doesn't always work well in multi-user environments. You should have a look at auto_increment[^] instead