I want to copy rows from one MySQL table to another while randomizing the order of two fields, i.e. I want to take rows with the fields id
,a
,b
from table1
and insert them into table2
, but swap a
and b
on random rows.
This is what I tried:
INSERT INTO table2 (id, a, b)
SELECT
id,
IF(@r=RAND() < 0.5, a, b),
IF(@r < 0.5, b, a)
FROM table1
WHERE table1.filter = 42;
I expected that this will generate one random number per row, but it actually just generates one random number per query, so it's either a,b for all rows or b,a for all rows. What do I need to change?
P.S: I'm using a variable because calling rand()
in both IFs will generate two seperate numbers and I could get the same field twice. Is there a way to to this without a variable?
Aucun commentaire:
Enregistrer un commentaire