How to create a random Number for menu_order in MySQL

  • I am trying to figure out how to create a MySql query that will fill the menu_order column for my custom post type with random numerical data that is unique for every post and is limited to a specific range.
    I have come across this select statement which outputs a random number and checks that number does not yet exist in a column:
    SELECT FLOOR(RAND() * 999) AS random_num FROM posts WHERE "random_num" NOT IN (SELECT menu_order FROM posts where post_type = 'my_cpt') limit 1 I would love to take the output of that statement and use it as the input for an update statement, but I can’t figure out how to do that.

    The second approach I found is this one, but it has failed in providing unique random numbers, i.e. there were already doubles after I ran it twice:
    UPDATE posts SET menu_order = crc32(uuid()) % 1000 WHERE post_type='my_cpt'

    Another question is if I can run an update query for new posts which creates a unique random number for posts of a specific post_type. How would I do that?

Viewing 1 replies (of 1 total)
  • Thread Starter BdN3504

    (@bdn3504)

    On the mysql irc channel this code was suggested:
    SET @rank := 0; UPDATE posts SET menu_order = @rank := @rank + 1 where gmr_posts.post_type = 'my_cpt' ORDER BY RAND();
    This works well enough, although i don’t really understand the code and i don’t know how to specify the range.

Viewing 1 replies (of 1 total)
  • The topic ‘How to create a random Number for menu_order in MySQL’ is closed to new replies.