mercredi 14 février 2018

Randomize order of two columns per row in a query

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