Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

I came up with this query:

$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]
)

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:

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

How could I solve this problem?

like image 833
luqo33 Avatar asked Feb 08 '23 15:02

luqo33


1 Answers

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';

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.

like image 73
Gordon Linoff Avatar answered Mar 05 '23 15:03

Gordon Linoff