Update a field in a MySQL table based on two fields from another

I have a custom post type in WordPress for employees and used a combination of first and last name as the post title. Both the first and last name are also stored in individual fields in the post meta table.

Later I decided to change the structure of the post title from last name comma first name to first and last name. That change was easily made and enforced for all future records, but I needed a quick way to update the post titles for all existing employees.

I wrote a little MySQL statement that accomplished this and thought I’d share, as it might come in handy to someone else in the future.

UPDATE wp_posts 
SET post_title = (SELECT concat(c0.meta_value, ' ', c1.meta_value) 
FROM wp_postmeta AS c0 
JOIN wp_postmeta AS c1 
ON c0.post_id = c1.post_id 
WHERE c0.meta_key = '_first_name' 
AND c1.meta_key = '_last_name' 
AND wp_posts.ID = c0.post_id) 
WHERE post_type = 'employee';

Let’s break this statement down:

• Line 1 refers to the wp_posts table that we’ll be updating.
• Line 2 sets the post_title field to the result of the following subquery, which selects and concatenates two fields (meta_value and meta_value) from two other tables (wp_postmeta and wp_postmeta), which are really the same table, but we have two instances so we can get values from two different rows.
• Line 3 lists the other table (wp_postmeta) that we’re getting the value from and gives it a temporary name of c0.
• Line 4 joins that table to itself in another instance (c1), so we can get another value from the same field, but a different row.
• Line 5 combines those two rows based on the value of post_id.
• Line 6 and 7 indicate which column (meta_key) we’re looking at and which meta_value we’re looking for, which is _first_name and _last_name.
• Line 8 brings the subquery home by connecting the row from wp_posts with our rows from wp_postmeta based on the post ID.
• Line 9 indicates that we only want to overwrite post titles for the employee post type.

If you have any questions or found this helpful, feel free to leave a comment below.

Featured image by Kaleb Tapp.