phpmyadmin Question posted in
The official documentation can be found here.

Query a table based on values from within that table – Phpmyadmin

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 want to filter them out using a SQL query.

I want to select everything in the DB, through PhpMyAdmin, for the rows that match one of the following criteria:

  1. post_type = "answer"
  2. post_type = "question"
  3. post_type contains revision, preceded by the ID of either one of the previous criteria. For example: 21-revision-v1 or 10903-revision-v1 Where I want to select those posts of which the first numerical part matches the ID of posts selected in the previous 2 requirements.

I am a complete novice to SQL so I started with some googling, and found the concept of Temporary Tables. Which lead me to create this bit of code:

SELECT *  INTO #QA 
FROM `wp_posts` WHERE 
`post_type` = "answer" OR
`post_type` = "question"

However I get the following error:

#1064 – Er is iets fout in de gebruikte syntax bij ‘FROM wp_posts WHERE
post_type = "answer" OR
post_type = "question" LIMI’ in regel 2

Which translates to "There is somthing wrong with the syntax near"….

Is what I am attempting even feasible?

Tags:

3

Answers


  1. 0

    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')
    Login or Signup to reply.
  2. 0

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

    CREATE TABLE tablename AS
SELECT * FROM `wp_posts` 
WHERE `post_type` = 'answer' OR `post_type` = 'question'
    Login or Signup to reply.
  3. 0

    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 AS
    SELECT 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search