UPDATE SQL Table with random unique numbers

Question

Hi I have a trouble with SQL UPDATE. I want to insert random unique number from 0-2 to column draft in table draft (where column lobby is 1).

I have this, but it ends with error: #1093 - You can't specify target table 'draft' for update in FROM clause

UPDATE draft 
SET draft = (
  SELECT FLOOR(RAND() * 3) AS random_num 
  WHERE "random_num" NOT IN (
    SELECT draft FROM draft
  )
) 
WHERE lobby = 1

RAND range will be generated dynamic on app level so the result should be like this: 3 records = unique random 0-2, 9 records = unique numbers from 0-8 etc.

Start

ID DRAFT LOBBY
1  null  1
2  null  1
3  null  1

Result

ID DRAFT LOBBY
1  1     1
2  2     1
3  0     1

Any help please?

Edit

I updated the query to this:

UPDATE draft 
SET draft = (
  SELECT FLOOR(RAND() * 3) AS random_num 
  WHERE "random_num" NOT IN (SELECT draft FROM (SELECT * FROM draft) AS temp)
) 
WHERE lobby = 1

No syntax error apears, but doesnt change any row, dont know why.

Tag your question with the database you are using. Please provide sample data and desired results. Three random numbers doesn't give you much choice. — Gordon Linoff, Nov 03 '19 at 22:30
Does this answer your question? [You can't specify target table for update in FROM clause](https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause) — Peter O., Nov 03 '19 at 22:32
Is the problem you are trying to solve persisting to the db a random sort order or something like that? Sounds like you want to do it the other way around, i.e. initialise all records to null, `update draft set draft = THENUMBER where draft IS NOT NULL ORDER BY RAND() LIMIT 1` - in a loop — AD7six, Nov 03 '19 at 22:37

Gordon Linoff · Answer 1 · 2019-11-03T23:06:12.807

You could do:

update draft d cross join
       (select n.*
        from (select 0 as n union all select 1 as n union all select 2) n
        where n.n not in (select d2.draft from draft d2)
        order by rand()
        limit 1
       ) n
    set d.random_number = n.n
    where d.lobby = 1
    limit 1;

Note the limit 1. This allows you to update one row at a time. If multiple rows meet the condition, then you should repeat the update with appropriate filtering criteria.

forpas · Answer 2 · 2019-11-03T23:43:54.777

In your query, you probably think that this:

WHERE "random_num" NOT IN...

compares the column random_num against the values after IN, but this is not the case.
Why? Because a computed column like random_num cannot be used in the WHERE clause
What it is doing is comparing the string "random_num" which is converted to 0 in order to perform the comparison.

This will work if the ids are (like your sample data) 3 consecutive integers:

update draft d cross join (
  select group_concat(t.x order by rand() separator '') col
  from (select 0 x union all select 1 union all select 2) t
) r
set d.draft = substr(r.col, d.id % 3 + 1, 1)
where d.lobby = 1;

The column draft will be updated with 3 unique random integers in the range 0-2.
See the demo.

UPDATE SQL Table with random unique numbers

Edit

2 Answers2