Resolved - Need help with a MySQL query

[Dirty Butter]

I need help with our Wordpress database, and this is the only place I'm likely to find someone who can tell me how to do what I need to do.

 

I created this query and it correctly allowed me to export a csv file with the post_id and meta_value I need in ascending order by post_id.

 

SELECT `post_id`, `meta_key`, `meta_value` FROM `wp_postmeta` WHERE `post_id` > 8721 AND `meta_key` LIKE '_yoast_wpseo_metadesc'

 

 

 

 

Now I need to get the result from the meta_value column for each post_id into a different table, wp_posts.

 

 

The post_id in the wp_postmeta table corresponds to the ID column in the wp_posts table.

 

The meta_value in the wp_postmeta table corresponds to the post_excerpt column in the wp_posts table.

 

 

Could someone please offer a query that would do what I need?

[bsmither]

"Now I need to get the result from the meta_value column for each post_id into a different table, wp_posts."

 

Would a better way of saying this be:

Now I need to get the value of the post_excerpt column from the table, wp_posts, for each of the post_id's and join the recordsets from both tables.

 

Also,

The post_id in the wp_postmeta table corresponds to the ID column in the wp_posts table.

The meta_value in the wp_postmeta table corresponds to the post_excerpt column in the wp_posts table.

 

That's two direct relationships. That's redundant. Basing the following statement only on the fact that Wordpress has been around for a while, I'm sure the database designers didn't do this and maybe you are mistaken in your interpretation of what you are seeing in your effort to get what you need. One of these relationships (probably not both) should be a one-to-many relationship. The other might not be a relationship at all.

 

Other than the 'into a different table' phrase, let's say you are absolutely right.

 

So, try:

SELECT `post_id`, `meta_key`, `meta_value`, `post_excerpt`
FROM `wp_postmeta`
LEFT JOIN `wp_posts`
ON `wp_postmeta`.`post_id` = `wp_posts`.`ID`
WHERE `post_id` > 8721 AND `meta_key` LIKE '_yoast_wpseo_metadesc';

[Dirty Butter]

Thank you so much for responding, Bsmither!! I depend on you so much for CC help, and I really appreciate your willingness to weigh in on this WP issue. The postmeta table is from a plugin and the posts table is the default WP table. WP automatically creates a shortened excerpt from the post itself, but the normally empty post_excepts column provides a place for a custom excerpt. I'm trying to copy the values from plugin meta_value column into that default empty column meant for custom excerpts.

 

I worked on this some more last night and realized that the ID and post_id columns do NOT have a one to one correspondence. I've had to figure out some way to separate the revision copies of each ID number in the wp_posts table. I came up with this query that gave the list I need:

 

SELECT `ID`, `post_excerpt`, `post_status` FROM `wp_posts` WHERE `ID` > 8721 AND `post_status` LIKE 'publish

[Dirty Butter]

So would this query get the value of the meta_value column from the table, wp_postmeta, for each of the correct post_id's and replace the value of the post_excerpt column, where the post_id values meet certain criteria AND also the ID values meet certain criteria? The rest of the wp_posts table columns need to be left as is, and the wp_postmeta table needs to be left unchanged.

 

SELECT `post_id`, `meta_key`, `meta_value`, `post_excerpt`
FROM `wp_postmeta`
LEFT JOIN `wp_posts`
ON `wp_postmeta`.`post_id` = `wp_posts`.`ID`

WHERE `post_id` > 8721 AND `meta_key` LIKE '_yoast_wpseo_metadesc';

WHERE `ID` > 8721 AND `post_status` LIKE 'publish';

 

I'll back up these two tables and give this a try.

[Dirty Butter]

Well, I did get this to do something, but not what I wanted:

 

SELECT `post_id`, `meta_key`, `meta_value`, `post_excerpt`
FROM `wp_postmeta-test`
LEFT JOIN `wp_posts-test`
ON `wp_postmeta-test`.`post_id` = `wp_posts-test`.`ID`

WHERE `post_id` > 8721 AND `meta_key` LIKE '_yoast_wpseo_metadesc' AND `ID` > 8721 AND `post_status` LIKE 'publish'

 

This query added the post_excerpt column to the wp_postmeta-test table. This is the opposite of what I want to do, which is to add the meta_value column to the wp_posts-test table. If I can get it to do that I can always delete the post_excerpt column for the wp_posts-test table and rename the newly added meta_value column.

[Dirty Butter]

The developer of my WP theme found a bug in the stock WP code that caused my problem!!! So, thank goodness I don't need to worry about the query after all. I did learn a tiny bit about queries, and I really appreciate your help on this non CC issue.