MySQL: Possible to replace all of wp_posts.post_content(id#) via UPDATE + REPLACE + SELECT?

Question

EDIT: I want to update one field (post_content) within a known record (id of 4) of a known table (wp_posts) in a new database (new_db). The update has to come from one field (post_content) within a known record (id of 5) of a known table (wp_posts) in the old database (old_db). Is this possible, or is something like this too atomic (?) for MySQL?

Greetingz! When manipulating WordPress's database directly, I've seen many examples here where people replace a URL like so;

mysql> UPDATE wp_db.wp_post SET post_content=REPLACE(post_content, 'http://oldsite', 'http://newsite' ) ;

However, I have a .sql dump file from my old site, that I wish to import correctly. (I cannot create an XML export file, it's too late) I have created "dummy" posts on my new blog, and have imported the old database. Now I'm trying to do something like this;

UPDATE new_db.new SET post_content = REPLACE(post_content,(SELECT post_content FROM old_db.wp_posts WHERE id = oldpost#) WHERE id = newpost#;

I realize I'll have to do this for post_date, post_title, and post_content between old_db.wp_posts and new_db.posts, I'm just hoping this is actually possible somehow....

Answer 1

Seems like trying to put "field X" into db.table.record.fieldY is too atomic, or requires a better magic wand than google can find. I've gone a head and just ripped the posts out wholesale and injected them into the new DB. Here's how I did it. I hope it helps the next poor sod who finds himself in the same place I was...

How to rip out and inject WordPress posts from an old database into a new one.

0) Make a backup first of the new DB.

mysqldump -u root -p new_db >> dump-new_db-datetime.sql

1) first, find all your old published posts & pages;

SELECT id,post_date,post_title FROM old_db.old_posts WHERE
 post_type = 'post' AND post_status = 'publish' ORDER BY id;

2) Delete all your revisions with;

DELETE FROM old_posts WHERE post_type = "revision";

3) inject your old posts & pages into the new DB

INSERT INTO new_db.new_posts
  (
  post_author,
  post_date,
  post_date_gmt,
  post_content,
  post_title,
  post_excerpt,
  post_status,
  comment_status,
  ping_status,
  post_password,
  post_name,
  to_ping,
  pinged,
  post_modified,
  post_modified_gmt,
  post_content_filtered,
  post_parent,
  guid,
  menu_order,
  post_type,
  post_mime_type,
  comment_count
  )
SELECT
  old_posts.post_author,
  old_posts.post_date,
  old_posts.post_date_gmt,
  old_posts.post_content,
  old_posts.post_title,
  old_posts.post_excerpt,
  old_posts.post_status,
  old_posts.comment_status,
  old_posts.ping_status,
  old_posts.post_password,
  old_posts.post_name,
  old_posts.to_ping,
  old_posts.pinged,
  old_posts.post_modified,
  old_posts.post_modified_gmt,
  old_posts.post_content_filtered,
  old_posts.post_parent,
  old_posts.guid,
  old_posts.menu_order,
  old_posts.post_type,
  old_posts.post_mime_type,
  old_posts.comment_count
FROM old_db.old_posts;

Answer 2

It is possible what you want in an easy way. Have a look at http://spectacu.la/search-and-replace-for-wordpress-databases/; that will do all replaces automatically while also keeping length specifications of serialized data correct. Cheers, Peter