CODEWITHSUNDEEP
mysqlsql-updatesubquery
luqo33
luqo33

Reputation: 8361

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?

Upvotes: 5

Views: 3555

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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.

Upvotes: 7

Strawberry
Strawberry

Reputation: 33945

 UPDATE wp_usermeta x
   JOIN wp_usermeta y
     ON y.user_id = x.user_id 
    SET x.meta_value = y.meta_value
  WHERE y.meta_key = 'nickname'
    AND x.meta_key = 'first_name'
    AND x.user_id = %d;

Upvotes: 2

Related Questions