1

I'm trying to migrate one DB into another using only MySQL, it's two WordPress databases and I want to put all the posts of a specific post type (table wp_posts) and all their respective post metas (table wp_postmeta, uses wp_posts ID). My logic was to use a loop with an offset so I could insert the post and then use the LAST_INSERT_ID() to put the postmeta.

Sorry if this sounds confusing, I'm not sure how to explain it very well, and also if you don't know WP's structure what I wrote on the last paragraph might do absolutely nothing if you know MySQL. Either way, here's the code I was using which gives me an error:

/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END LOOP main' at line 1 */

SET @offsett := 0;
SELECT @maxx := COUNT(*) FROM quest.wp_posts WHERE post_type = 'page';
SET @indexx := 0;
main: LOOP
    SET @indexx := @index + 1;
    IF @indexx > @max THEN
        LEAVE main;
    END IF;
    SELECT @post_id := ID FROM quest.wp_posts WHERE post_type = 'pages' LIMIT 1 OFFSET @offsett;
    INSERT INTO new_quest.wp_posts (post_author, post_date, post_date_gmt, post_content, post_title, post_name, post_type) SELECT 1, qp.post_date, qp.post_date_gmt, qp.post_content, qp.post_title, qp.post_name, 'peterete' FROM quest.qp WHERE post_type = 'page' LIMIT 1 OFFSET @offsett;
    INSERT INTO new_quest.wp_postmeta (post_id, meta_key, meta_value) SELECT LAST_INSERT_ID(), qp.meta_key, qp.meta_value FROM quest.wp_postmeta qp WHERE post_id = @post_id;
    SET @offset := @offset + 1;
END LOOP main;

Could anyone point me in the right direction, please?

Dogmatics
  • 67
  • 8
  • any particular reason you are not using INSERT ... SELECT ? – e4c5 Sep 28 '15 at 05:05
  • @e4c5 Well, thing is I need it to insert the post but I don't want to use the old ID, then I need to insert each post's meta and since the ID is auto_increment the only way I could think to do it was with a loop – Dogmatics Sep 28 '15 at 14:59
  • What is stopping you from selecting the other columns in the table and leaving out the id field? – e4c5 Sep 28 '15 at 22:52

0 Answers0