Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


[MySQL] Problem with an UPDATE and getting a number.
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

[MySQL] Problem with an UPDATE and getting a number.

netomxnetomx Moderator, Veteran

Will try to be as clear as possible. I have a table in MySQL who has registry of accounts, and each account may have up to 9 registries at their name. This will be defined in a column, called "registry_id", and it is from 1 to 9. This number will be at random. The detail here is, I want to move one of the registries from an account to another, but I can't get 2 duplicated registy_id on an account, so I need to make another number for it, between 1 and 9, but not duplicated.

Any help on this? I'm trying to make it without making a PHP function, I want to know if it is possible in a sentence in MySQL.}

Thanks!

Comments

  • You can use a stored mysql function, something like the one i knocked up below:

    DELIMITER $$ DROP FUNCTION IF EXISTS rand_reg; CREATE FUNCTION rand_reg (i INT) RETURNS int BEGIN DECLARE n INT; sloop:LOOP SELECT FLOOR(RAND()*9)+1 INTO n; IF n NOT IN (SELECT reg_id FROM reg WHERE id=i) THEN LEAVE sloop; END IF; END LOOP; RETURN n; END $$ DELIMITER ;

    You should then be able to call rand_reg(id) in your statements, example:

    UPDATE reg SET reg_id = rand_reg(i) WHERE id=i;

    Ofcourse, for safety you would want to make sure that the total number of unique enteries for 'i' is <9, otherwise it would be an infinate loop ;)

  • netomxnetomx Moderator, Veteran

    Thank you! Will try tonight :)

  • netomxnetomx Moderator, Veteran

    @r0t3n why the RAND? Does ++ exists in MySQL ?

  • need field + 1, no ++

  • netomxnetomx Moderator, Veteran

    maybe:

    DECLARE n INT 1; sloop:LOOP SELECT n + 1 INTO n; IF n NOT IN (SELECT reg_id FROM reg WHERE id=i) THEN LEAVE sloop; END IF; END LOOP; RETURN n;
    ??

  • You can do it like that, but the code would look more this like:

    DELIMITER $$ DROP FUNCTION IF EXISTS rand_reg1; CREATE FUNCTION rand_reg1 (i INT) RETURNS int BEGIN DECLARE n INT; SET n = 1; sloop:LOOP IF n NOT IN (SELECT reg_id FROM reg WHERE id=i) THEN LEAVE sloop; ELSE SET n = n + 1; ITERATE sloop; END IF; END LOOP; RETURN n; END $$ DELIMITER ;

    But, the number would not exactly be 'random', as it would just be the next number in the list kind of thing, and you might need to put another break clause in to kill the loop if n >=9, but that should not be needed if there is never more than 9 enteries for the given account.

  • netomxnetomx Moderator, Veteran

    @r0t3n doesn't need to be random, I just need it to be between 0 and 9, so no problem there. Btw, before your last post, I made this, and it is working:

    DELIMITER $$ DROP FUNCTION IF EXISTS rand_reg; CREATE FUNCTION rand_reg (i INT) RETURNS int BEGIN DECLARE n INT DEFAULT 0; sloop:LOOP IF n NOT IN (SELECT char_num FROM northro_classic.char WHERE account_id = i) THEN LEAVE sloop; END IF; SELECT n + 1 INTO n; END LOOP; RETURN n; END $$ DELIMITER ;

    However, it seems the function is lost if I restart the server.

    So, the functions works fine now, thanks for your help! I didn't find anything like this on Google. :)

Sign In or Register to comment.