44

I would like to add a random value to a table. While I know how to add random integers within a range, I am currently stumped on how to add a randomly selected item from a list.

Let's say I have a MYSQL table for IM accounts. I would like to fill it with random data.

INSERT INTO `im` (`im`, `service`)
SELECT LOWER(`last`), RANDOM-SELECTION-HERE
FROM `contact`;

What this query should do is add to the IM table the contact's last name with a random selection from an array I would give. For example, the array would be:

['AIM', 'ICQ', 'MSN', 'Yahoo', 'GTalk', 'Other']

So, I would like to add the users last name plus one of the random items from the array.

NOTE: I know this is fully possible with a programming language such as PHP, Perl, etc., but I am not trying to do that. Please try to provide a way to do this strictly with MYSQL.

Improve this question

2 Answers 2

Reset to default
100 
INSERT INTO `im`
(`im`, `service`)
SELECT LOWER(`last`),
    ELT(0.5 + RAND() * 6, 'AIM', 'ICQ', 'MSN', 'Yahoo', 'GTalk', 'Other')
FROM `contact`
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Worked beautifully and easily to understand. Thank you :)
Note that 0.5 + RAND() * 6 will return values between 0 and 6 inclusive, with 0 returning a NULL value from ELT(). 1 + RAND() * 6 will return values between 1 and 6 inclusive which will mean ELT() always returns a result from the list.
@AndyBurton: Incorrect. The first argument to ELT(), if floating-point, is rounded to produce an index; 0.5 plus any number >= 0 cannot round to 0.
@AndyBurton: Also, 1 + RAND() * 6 produces values from 1 to 7 inclusive, thereby exceeding the list size and producing NULLs.
@chaos my apologies i'd wrapped it in FLOOR whilst flicking back to the MySQL docs, which was producing 0-6 inclusive, i changed it to FLOOR(1+RAND()*6) to get 1-6 inclusive. The mysql line was To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j – i)).
|
12

If you're willing to add the services to another table...

INSERT INTO `im` ( `im` , `service` )
SELECT LOWER( `last` ) , (
   SELECT `service`
   FROM `service`
   ORDER BY RAND( )
   LIMIT 1
)
FROM `contact`
Improve this answer

1 Comment

that was awesome, exactly what i was looking for

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.