SQL查询使用通过随机选择另一个表中的值来替换一个表中的值

Question

I have a table with an value that I would like to be updated periodically with a cron job. However, I need to update the value by replacing it with a value from a different table. The issue is that I would like the replacement value to be chosen randomly.

For example, Table 1 has

ID    Email
=================
1     bobatumail

Table 2 has:

ID    Email
================
1     bobatumail
2     joeatumail
3     peteatumail
4     biffatumail
5     wilneratumail
6     wilsonatumail

I would like the query to replace bobatumail in Table 1 with any of the other values in Table 2 as long as it is random. It could even be the same value as in Table 1.

Any idea how to do this?

Answer 1

In MySQL you could use the REPLACE statement:

REPLACE INTO table1 (ID, Email)
SELECT 1, table2.Email FROM table2 ORDER BY RAND() LIMIT 1;

The "1" in the second line represents the id of the entry while the second part returns a random value out of table2. Yes, there are solutions using the UPDATE statement (JOIN and ANSI) but its always tricky and you usually have to turn off safe update mode.

http://dev.mysql.com/doc/refman/5.5/en/mysql-command-options.html#option_mysql_safe-updates

Please note that REPLACE first deletes the old entry and then reinserts the new one.

http://dev.mysql.com/doc/refman/5.5/en/replace.html