Best way to update MySQL table with random values from another table

Question

For this (pseudo code) example I have two tables in MySQL:

member { id, name }
names { name }

There are 100 members in member and 10 names. I want to use a random name from names to update the member table. So far I've got this, but, not sure if there is a better method to achieve it.

UPDATE member SET name = (SELECT name FROM names ORDER BY RAND() LIMIT 1);

The code will be executed from a script so I'm looking to avoid functions etc.

Thanks in advance.

Answer 1

You could avoid ordering by rand() by adding id column to your names table and using:

UPDATE member SET name = (SELECT name FROM names WHERE id=floor(1 + rand()*10 ) );

With only 10 names the result won't be much faster, but you would see the difference if you wanted to choose from a bigger set of names as sorting by rand() starts being inefficient quite fast and you do it for every row in members.

Update: Seems like rand() inside where gives unpredictable results. Use this one instead:

UPDATE member m1
JOIN ( select id, floor(1+rand()*10) as rnd from member ) m2 on m1.id=m2.id
JOIN names n on n.id = m2.rnd
SET m1.name=n.name

Number of rows affected may vary, if random name matches the one already in the table it doesnt count as update.

Answer 2

Tried to improve piotrm's solution. Seems it works;-)

CREATE TABLE member (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE names (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO member VALUES 
  (1, NULL),
  (2, NULL),
  (3, NULL),
  (4, NULL),
  (5, NULL),
  (6, NULL),
  (7, NULL),
  (8, NULL),
  (9, NULL),
  (10, NULL),
  (11, NULL),
  (12, NULL),
  (13, NULL),
  (14, NULL),
  (15, NULL);

INSERT INTO names VALUES 
  (1, 'text1'),
  (2, 'text2'),
  (3, 'text3'),
  (4, 'text4'),
  (5, 'text5'),
  (6, 'text6'),
  (7, 'text7'),
  (8, 'text8'),
  (9, 'text9'),
  (10, 'text10');

UPDATE
  member m1
  JOIN (SELECT id, @i:=FLOOR(1 + RAND() * 10), (SELECT name FROM names n WHERE n.id = @i) name FROM member) m2
    ON m1.id = m2.id
SET
  m1.name = m2.name;