Query A Table Based On Values From Within That Table

We have a WordPress site that contains a Q&A plugin, that needs to be moved to a different hosting. The Q&A posts only make up for a fraction of the total Posts table, so I

Solution 1:

MySQL does not support the select ... into ... syntax to write to a table, as pinpointed in the documentation.

Instead you can use insert ... select:

insert into `#qa`select *
from wp_posts
where post_type in ('question', 'answer')

Solution 2:

The syntax that you are using is most commonly associated with SQL Server. MySQL uses the (more common) create table as syntax. And, it allows specifically for temporary tables in the syntax.

So, the equivalent in MySQL is CREATE TABLE AS:

CREATE TEMPORARY TABLE QA ASSELECT p.*FROM wp_posts p
    WHERE post_type IN ('answer', 'question');

Note that a temporary tables is a very specific type of table that exists only in the current "session" -- say, your current connection to the database. It is not visible to other users and it will disappear when you reconnect to the database.

Solution 3:

The SELECT * INTO... syntax is used to create a new table. If this is what you want then the syntax for MySql is:

CREATETABLE tablename ASSELECT*FROM `wp_posts` 
WHERE `post_type` ='answer'OR `post_type` ='question'