Referencing the same table both as target of UPDATE and source of data in MySql

Question

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?

Gordon Linoff · Accepted Answer · 2016-03-01T02:00:35.137

7

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.

edited Mar 01 '16 at 02:00

answered Feb 29 '16 at 23:00

Gordon Linoff

1,242,037
58
646
786

Oh, you beat me to it – Strawberry Feb 29 '16 at 23:15
This is great thank you. Only small change `meta2 = 'nickname'` should be `meta2.meta_key='nickname'`. – luqo33 Feb 29 '16 at 23:19
Can `UPDATE wp_usermeta meta JOIN` be translated into `UPDATE wp_usermeta AS meta JOIN`? This is in fact a labeling right? – 71GA Sep 19 '17 at 18:54
1

@71GA . . . You can use `as` for table aliases. I don't, but you can if you like. – Gordon Linoff Sep 20 '17 at 01:31
Very nice solution ! – viniciusalvess Nov 11 '17 at 12:43
Perfect solution. It worked for me. Thanks a lot. – Sohel Pathan Jun 14 '23 at 10:48

score 2 · Answer 2 · answered Feb 29 '16 at 23:15

2

 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;

answered Feb 29 '16 at 23:15

Strawberry

33,750
13
40
57

Referencing the same table both as target of UPDATE and source of data in MySql

2 Answers2

Linked