CODEWITHSUNDEEP
mysqlsqlrandom
travelboy
travelboy

Reputation: 2697

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?

Upvotes: 3

Views: 119

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272446

Using @variables seems to work but MySQL discourages assigning and reading values to variables inside same statement because:

... the order of evaluation for expressions involving user variables is undefined.

A better solution is as follows:

-- INSERT INTO ...
SELECT
  id,
  IF(r < 0.5, a, b),
  IF(r < 0.5, b, a)
FROM (
  SELECT id, a, b, RAND() AS r
  FROM table1
  WHERE table1.filter = 42
) x

Upvotes: 2

Kedar Limaye
Kedar Limaye

Reputation: 1041

INSERT INTO table2 (id, a, b)
set @r=0;
  SELECT
    id,
    IF(@r=RAND() < 0.5, a, b),
    IF(@r        < 0.5, b, a)
  FROM table1
  WHERE table1.filter = 42;

Upvotes: 0

Related Questions