Search code examples
phpmysqljoinsql-order-byinner-join

How to make MySQL select a random winner when using INNER JOIN

SELECT * FROM `surveys` INNER JOIN `surveyusers` ON `surveyusers`.`survID` = `surveys`.`survID` WHERE `surveyusers`.`hasWon` = 0 ORDER BY RAND();
UPDATE surveys SET winner = email;

This was a school assignment that required us to select and update the survey winner.

I use PHP to manage and show surveys, I want the SQL to pick the winner. PHP sets survID

I want to pick a random winner and set the winner coloumn in surveys table to the email in the surveyusers table. But I really have a lot of trouble with it.

Solution

  • Updates can take the same syntax as a Select.

    You can combine these two queries into one.

    UPDATE `surveys` `s` SET `s`.`winner` = (
    SELECT email FROM `surveyusers`
    WHERE `surveyusers`.`hasWon` = 0
    AND `survID` = 1
    ORDER BY RAND()
    LIMIT 1
) WHERE `survID` = 1;

    The survID you would have to set from PHP of course.