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
SET param_last_tier = (SELECT MAX(tier_number) FROM order_tiers WHERE order_id = param_order_id) + 1;
SET param_last_tier = 1;
If your tier number will be ever changing, I suggest you do not have it stored in your DB. Instead you should generate a counter on the tiers when they are selected either (preferably) in your presentation layer or in the SQL that selects the tiers.