duanli12176
2012-11-20 20:39
浏览 76
已采纳

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

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?

图片转代码服务由CSDN问答提供 功能建议

我有一个表格,我想用cron作业定期更新。 但是,我需要通过将其替换为来自不同表的值来更新该值。 问题是我希望随机选择替换值。

例如,表1有

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

表2包含: < pre> ID Email ================ 1 bobatumail 2 joeatumail 3 peteatumail 4 biffatumail 5 wilneratumail 6 wilsonatumail < / pre>

我希望查询将表1中的bobatumail替换为表2中的任何其他值,只要它是随机的。 它甚至可以与表1中的值相同。

知道如何做到这一点吗?

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dsjz1119 2012-11-20 21:21
    已采纳

    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

    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题