Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    137

    Unanswered: How to update sequence in MySQL

    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

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •