CODEWITHSUNDEEP
mysqlsql
Ridai
Ridai

Reputation: 179

MySQL Copy data to another table WHERE id is the same

I've been having difficulty transferring data from my old table to the WordPress usermeta table for the past hour or two. My query so far looks like this:

UPDATE bitnami_wordpress.wp_usermeta 
SET bitnami_wordpress.wp_usermeta.meta_value = customer_db.customer_table.LastName
FROM customer_db.customer_table, bitnami_wordpress.wp_usermeta 
WHERE customer_db.customer_table.CompanyID = bitnami_wordpress.wp_usermeta.user_id
AND bitnami_wordpress.wp_usermeta.meta_key = 'last_name'

But it's a no go;

I'm trying to copy the LastName field from customer_table into the meta_value field on the wp_usermeta table, where the CompanyID from customer_table is the same as the user_id in the user_meta table.

Essentially giving everyone in the WordPress table a last name (and the same for first name).

Apologies for my bad MySQL code, but any input is greatly appreciated so I can learn for the future.

The error is:

MySQL said: Documentation 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM rbaukcom_rbacms.supportpassword, bitnami_wordpress.wp_usermeta WHERE rbau' at line 3

Upvotes: 0

Views: 118

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

You need to use JOIN UPDATE, also note that you are using different database so need to make sure that the user doing the update should have access to both of them

update bitnami_wordpress.wp_usermeta um
join customer_db.customer_table ct
on ct.CompanyID = um.user_id 
set um.meta_value = ct.user_id
where 
um.meta_key = 'last_name'

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

This is the syntax

UPDATE bitnami_wordpress.wp_usermeta as t1 inner join customer_db.customer_table as t2
ON t2.CompanyID = t1.user_id
       AND t1.meta_key = 'last_name' 
SET    t1.meta_value = t2.LastName

Upvotes: 2

Related Questions