SELECT a column then INSERT that column again randomly

Question

I've got a table:

player_id|player_name|play_with_id|play_with_name|

I made this table for a game.

Everyone who wants to play can sign up to it.

When they sign up the table stores player_id and player_name

When the period while they can sign up expires I want to assign every player_name to a play_with_name randomly.

So for example.. my structure would like this when they in sign up period:

player_id|player_name|play_with_id|play_with_name|
1          someone1
2          someone2
3          someone3
4          someone4
5          someone5

And this when the period expires:

player_id|player_name|play_with_id|play_with_name|

    1       someone1      2              someone2
    2       someone2      1              someone1
    3       someone3      4              someone4
    4       someone4      3              someone3
    5       someone5      -                 -

I think because there's no one left. Players 1-4 are already occupied with each other. — PerlDuck, Feb 04 '16 at 19:51
You're probably better off randomly assigning players in your application/the "business layer" instead of SQL. I tried to put together a query to do this just in case you need to do it in SQL, but unfortunately I can't get a response out of SQL Fiddle and I don't have MySQL available to me at the moment. — Tom H, Feb 04 '16 at 20:14
@PerlDog But they don't have to be, assuming the matches are actually random, and not in pairs — Blorgbeard, Feb 04 '16 at 20:36
You wouldn't normally store both name and the id (except in the player table) — Strawberry, Feb 04 '16 at 20:39
@Blorgbeard Yes. depends on the game. I thought of chess, for example, not paintball ;-) It's a nice riddle, btw. I'm still into it. But I'd suggest two tables `players` with just id and name, and `plays_with` with just two ids. I also agree that this is better shift up the "business layer" but I like it anyway. — PerlDuck, Feb 04 '16 at 20:40
If player_id column is an autoincrement primary key, you can introduce another column to store the order of registration (starting from 1). This would help to write an algorithm in sequel query to assign each player. Primary key column will cannot be used except for the first time when it starts with one. — Slowcoder, Feb 04 '16 at 20:57

score 2 · Accepted Answer · answered Feb 04 '16 at 20:43

I can't test this since I don't have a MySQL database handy and SQLFiddle seems to take forever to run anything, but this hopefully gets you there or at least close:

SET @row_num = 0;
SET @last_player_id = 0;

UPDATE P
SET
    play_with_id =
        CASE
            WHEN P.player_id = SQ.player_id THEN SQ.last_player_id
            ELSE player_id
        END
FROM
    Players P
LEFT OUTER JOIN
(
    SELECT
        @row_num := @row_num + 1 row_num,
        @last_player_id last_player_id,
        @last_player_id := player_id player_id
    FROM
        Players
    WHERE
        MOD(@row_num, 2) = 0
    ORDER BY
        RAND()
) SQ ON SQ.player_id = P.player_id OR SQ.last_player_id = P.player_id

The code (hopefully) sorts the players randomly then it pairs them based on that order. Every other player in the randomly sorted result is paired with the person right before them.

In MS SQL Server RAND() would only be evaluated once here and wouldn't end up affecting the ORDER BY, but I think that MySQL handles RAND() differently and generates a new value for each row in the result set.

score 0 · Answer 2 · edited May 23 '17 at 11:52

0

I'm not sure why some client code isn't doing this as opposed to having this operation be done at the database level, but I suppose if you get the strategy for retrieving a randomized row set based on your DB from here, you could then write a stored procedure with a cursor or iterator to loop through the result set of something like:

select player_id, player_name from players order by RAND()

and then loop through the all the table rows to update the play_with_id and play_with_name, where the previously selected player_id <> play_with_id.

edited May 23 '17 at 11:52

Community

1
1

answered Feb 04 '16 at 20:19

Dave

161
1
7

The database is tagged in his question: mysql. – Tom H Feb 04 '16 at 20:27
Ah, so it is. Thanks. – Dave Feb 04 '16 at 20:29

SELECT a column then INSERT that column again randomly

2 Answers2