UPDATE values from a table to another using rand() in MySQL

Question

I have 2 tables and I need to UPDATE the value of table1 for uid = 0.

Here my tables:

table1: games

id      | uid     | 
--------+---------+
1       | 5       | 
2       | 7       | 
3       | 0       |

table2: users

idu     | name    | 
--------+---------+
1       | todd    | 
2       | mario   | 
3       | luigi   |

So basically I need to UPDATE the uid value of the 3rd row of table1 with a random number between 1-3 (idu values of table2).

For the purpose I tried this:

UPDATE games (uid)
SET idu = ORDER BY rand() LIMIT 1 FROM users
WHERE uid = 0;

Why it's not working?

Actually, this may help: http://stackoverflow.com/a/6550197/2737474 — Gerardo Charles Rojas Vega, Jun 20 '15 at 16:52
Not clear what would be the expected result after the update is done ? — Abhik Chakraborty, Jun 20 '15 at 16:55
@AbhikChakraborty I wrote" I need to UPDATE the uid value of the 3rd row of table1 with a random number between 1-3 (idu values of table2)." — NineCattoRules, Jun 20 '15 at 16:56
@CharlesRojas thanks but I don't need to random 2 tables...`rand() LIMIT 1` should be correct — NineCattoRules, Jun 20 '15 at 16:58
@AbhikChakraborty I need to update the `0` value of `uid` with a random `idu`...I don't know how to better explain — NineCattoRules, Jun 20 '15 at 17:01

Rahul · Accepted Answer · 2015-06-20T17:28:02.990

2

Why can't you do like

UPDATE games SET uid = (
select idu from users ORDER BY rand() LIMIT 1)
WHERE uid = 0;

Per your comment, in that case use a compound condition. So in your case use the ID column as well in condition like

WHERE uid = 0 AND ID = 3;

Which will make sure that only row with ID = 3 getting updated considering the fact that your ID column is primary key / unique key column.

edited Jun 20 '15 at 17:28

answered Jun 20 '15 at 17:17

Rahul

yes this is really nice...but is it possible to use it for `UPDATE` only the first row with `uid=0` and not all rows with `uid=0`? – NineCattoRules Jun 20 '15 at 17:21
Whichever row matches the condition `uid = 0` will be updated. – Rahul Jun 20 '15 at 17:22
I wish to get a different random number for each `uid=0` . Any possible solution? – NineCattoRules Jun 20 '15 at 17:25

score 1 · Answer 2 · answered Jun 20 '15 at 17:06

1

I guess this might help you

SELECT @idu := idu FROM users ORDER BY RAND() LIMIT 1;
UPDATE games SET uid = @idu WHERE uid = 0;

However, this will update ALL rows where games.uid = 0 with the same randomly fetched users.idu

answered Jun 20 '15 at 17:06

Eduardo Escobar

damn :) ...is there any possible solution to avoid it? I need to have diifferent random value for each cell that has 0 value – NineCattoRules Jun 20 '15 at 17:13

2 Answers2