MySQL: Copy a field to another table

Question

I have a table posts that could look like this:

  id  |  title  |  body  |  created  | ..
-------------------------------------------

I would like to use the boolean search feature that is offered by a MyISAM Table, but the posts table is InnoDB. So I created another table 'post_contents' that looks like this:

  post_id  |  body
--------------------

That table is already filled with some contents and I can use the boolean search. However, I need to move the title field in the post_contents table as well and then copy the existing title-data to the new field.

I know about the INSERT .. SELECT syntax, but I don't seem to be able to create the correct query.

Answer 1

Did you try

insert into post_contents (post_id, body) select id, body from posts;

Or is the post_id column in the post_contents table generated differently?

Answer 2

I found a way:

I copied the the post_contents table to pc and truncated the existing data in post_contents. Then I used that query

INSERT INTO post_contents (post_id, title, body, created, modified) 
SELECT post.id, post.title, pc.body, pc.draft, pc.created, pc.modified 
FROM posts 
INNER JOIN pc ON post.id = pc.post_id

Maybe that is helpful for other people :)