引用同一表作为UPDATE的目标和MySql中的数据源 [英] Referencing the same table both as target of UPDATE and source of data in MySql

问题描述

我想对本地数据库进行更新，在该数据库中，我要使某些字段的值与表中另一个字段的值相同.

I wanted to make an update against my local database where I'd make some of the fields have the same value as another field present in the table.

我想到了这个查询:

$wpdb->prepare(
    "
    UPDATE wp_usermeta meta
    SET meta.meta_value = (
      SELECT usermeta.meta_value
      FROM wp_usermeta usermeta
      WHERE usermeta.meta_key='nickname'
      AND usermeta.user_id = %d
    )
    WHERE meta.user_id = %d
    AND meta.meta_key='first_name'
    ",
    $userId[0],
    $userId[0]
)

查询将在PHP循环中运行，因此在每次迭代中，$userId都将不同.该查询是针对WordPress数据库运行的(但这与问题无关).

The query would be run in a PHP loop so on each iteration the $userId will be different. The query is run against WordPress database (but this should be irrelevant to the question).

尝试运行查询时收到以下错误:

I'm receiving the following error when attempting to run the query:

表'meta'被指定两次，既作为'UPDATE'的目标，又作为 单独的数据源

Table 'meta' is specified twice, both as a target for 'UPDATE' and as a separate source for data

我该如何解决这个问题?

How could I solve this problem?

推荐答案

一种方法是改用join:

UPDATE wp_usermeta meta JOIN
       wp_usermeta meta2
       on meta.user_id = meta2.user_id and
          meta2.meta_key = 'nickname'
SET meta.meta_value = meta2.meta_value
WHERE meta.user_id = %d AND meta.meta_key = 'first_name';

我可能建议在where子句中添加一些内容，例如meta.meta_value is not null，以防万一名字已经被填充.但是，您似乎想要复制该字段，而上面的操作就是这样.

I might suggest adding something to the where clause such as meta.meta_value is not null, just in case the first name is already populated. However, you seem to want to copy the field, which is what the above does.

这篇关于引用同一表作为UPDATE的目标和MySql中的数据源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！