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_poststable that we’ll be updating.
- Line 2 sets the
post_titlefield to the result of the following subquery, which selects and concatenates two fields (
meta_value) from two other tables (
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
- 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
- Line 6 and 7 indicate which column (
meta_key) we’re looking at and which
meta_valuewe’re looking for, which is
- Line 8 brings the subquery home by connecting the row from
wp_postswith our rows from
wp_postmetabased on the post ID.
- Line 9 indicates that we only want to overwrite post titles for the
If you have any questions or found this helpful, feel free to leave a comment below.
Featured image by Kaleb Tapp.