Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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

share|improve this question
    
You'll also have to update wp_postmeta and wp_term_relationships, plus wp_comments and wp_commentmeta if there are any comments. Look at codex.wordpress.org/File:WP3.0-ERD.png for the structure of the db. Why can't you import everything into an empty WP install, then export the posts in a more logical way? That might be easier if you're dealing with a large number of entries to import. –  goldenapples Mar 17 '11 at 17:10
    
I've made new posts into the new blog via QuickPress, so wp_postmeta, wp_term_relationships, and anything comment-related is out of the question. I'm only dealing with 30 or so posts. But I'm trying to develop a way that will scale to our other blog with 1500 published posts. –  user4004 Mar 17 '11 at 23:06

2 Answers 2

up vote 1 down vote accepted

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;
share|improve this answer

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

share|improve this answer
    
Peter, I appreciate the link. However the script is designed to replace strings within any table of any database (by default). However I do like how it treats serialized data (more stuff for me to google). I'm looking to replace a record's post_content as a whole, from old_db.wp_posts(id=1) to new_db.wp_posts(id=5). –  user4004 Mar 17 '11 at 11:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.