[Resolved] Changed post type in DB, posts not showing up in dashboard or front-end

Problem:
I used this SQL query to change the post_type of a bunch of posts:

UPDATE `wp_posts` SET `post_type` = 'post' WHERE `post_type` = 'news';

In essence, I wanted my "news" CPT to no longer be a CPT, but instead, to be regular posts. Most (all?) of these "news" posts have translations.

If I deactivate WPML, I see all the converted posts on /wp-admin/edit.php and on the front-end of the site.

If I activate WPML, the ex-CPT posts no longer appear on edit.php nor on the front-end.

I suppose I need to modify something else in the DB, but I've poked around all over the place, and I can't find what I need to modify. Can you assist? Note that I am currently testing on our Development site but intend to do the same on the Production site.

Solution:
As you mentioned, the issue you're facing happens because WPML has specific tables that store information about post translations(https://wpml.org/documentation/support/wpml-tables/#language-information-and-translations).

When you changed the post_type in the wp_posts table, the metadata in WPML's tables still pointed to the old "news" post type, which is causing the discrepancy.

Here's how we can address this:

- Backup your database. This ensures you can revert changes if anything goes awry.

- Update the wp_icl_translations table in your database to reflect the change you made. In essence, you'll be telling WPML that these posts are now standard posts, not the "news" CPT. This code is an example of how you could handle such change(Please note: the code provided is to be used as an informational one. remember that custom coding is out of the scope of our support so we can't create, debug or modify code for you and it's your responsibility to maintain it. we hope the one we used as an example could point you in the right direction):

UPDATE `wp_icl_translations` 
SET `element_type` = 'post_post'
WHERE `element_type` = 'post_news';

-After updating the database, refresh your permalink settings in WordPress. This often resolves odd behaviors after changing post types.

Once these changes are made, your posts should appear correctly both in the admin area and on the front-end, even with WPML activated.

Relevant Documentation:
https://wpml.org/documentation/support/wpml-tables/