Migrate posts from a Custom Post Type to another with SQL

I would like to migrate all the posts from my custom post type "cpt-project" to another existing custom post type named "cpt-publication".

I don't want to use any plugin to achieve it. I would prefer to use a SQL query through phpMyAdmin to make the migration but I don't find any info on how do it.

Thanks.

I would like to migrate all the posts from my custom post type "cpt-project" to another existing custom post type named "cpt-publication".

I don't want to use any plugin to achieve it. I would prefer to use a SQL query through phpMyAdmin to make the migration but I don't find any info on how do it.

Thanks.

• custom-post-types
• posts
• mysql
• sql
• migration

Share Improve this question asked Apr 16 at 16:50 Mathieu PréaudMathieu Préaud 20355 silver badges1818 bronze badges 2

• 1 what's the reason behind using just SQL? Doing it this way won't flush caches, update rewrite rules, or fire actions/filters that plugins and some themes would rely on to catch this. Otherwise there are shell/terminal/CLI options as well as PHP code options. Note that asking for a plugin recommendation isn't allowed here so people can't answer this question with the name of a plugin – Tom J Nowell ♦ Commented Apr 16 at 17:06
• @TomJNowell Thanks for the suggestions. The only reason is because I only know SQL. As you mentioned it seems there are better options. – Mathieu Préaud Commented Apr 16 at 17:14

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

WP CLI would be a better option. Assuming your host has it enabled, it shouldn't require installing any plugins.

wp post update $(wp post list --post_type=cpt-project) --post_type=cpt-publication

This searches for all posts of type cpt-project and updates their post type to cpt-publication.

The SQL answer:

UPDATE `wp_posts`
SET `post_type` = "cpt-publication"
WHERE `post_type` = "cpt-project"

This assumes your database table prefix is wp_. It will change the post type directly in the posts table.

As @tom-j-novell stated in the comments to your question, doing it this way does not invalidate caches or fire any hooks. But depending on your usecase that might be exactly what you are after.