引用同一个表作为 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 代替:

One method is to use join instead:

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屋！