Martin Beardmore Martin Beardmore - 6 months ago 6
SQL Question

Query to select one random Row without any repeats

I have a table with 3 columns, as follows:

Columns:


  1. ID

  2. Channel_Location

  3. Used



I would like to retrieve a random entry from the table and update
Used
column to
1
. However, when I run my code - shown below - it returns
0
rows and doesn't return any idea. I was wondering why is this case?

The code

UPDATE channels
SET Used = 1
WHERE ID IN (
SELECT ID
FROM (select ID
FROM channels
WHERE Used != 0
ORDER BY RAND()
LIMIT 1) x);

Answer

One way to address this issue, is to do as follows:

SET @uid := (SELECT ID FROM channels WHERE Used = 0 ORDER BY RAND() LIMIT 1);
UPDATE channels SET Used = 1 WHERE ID = @uid;
SELECT * FROM channels WHERE ID = @uid;