SQL code to take specific rows from one table and overwrite them in another

Transact-SQL
1

Hi,

I have a WordPress site and I am using phpMyAdmin to admin my databases. I have one live site setup with a staging site that I used to make a number of changes to a bunch of pages. I (stupidly) thought I could just push the 'posts' and 'posts_meta' tables down from my staging site to my live site, however, I didn't realise that all my Woocommerce products are also stored in my 'posts' table so doing that would overwrite all our new products.

That is the scenario, I am now attempting to make the update using SQL in phpMyAdmin. This will (my plan anyway) allow me to copy just the pages records from the stage 'posts' table to the live 'posts' table overwriting my old pages with my shiny new ones that I have on stage without overwriting my products. My problem is I haven't played with SQL properly for years and I am struggling to find how best to do this.

To be clear: i want to grab all the rows from the 'posts' table on my stage database where post_type = "page" and then add them to the 'posts' table on my live site ensuring they overwrite the rows with the same ID's.

I have tried two options (see below for each) and both didn't work giving a syntax error.

I first tried this:

UPDATE dbqa71w2mfxarx.i8qMCzNt9_posts
SET Enabled = false
WHERE id IN
(SELECT id FROM dbugxamc7km01t.i8qMCzNt9_posts WHERE `post_type`="page")

Then this:

UPDATE dbqa71w2mfxarx.i8qMCzNt9_posts ia
SET    enabled = FALSE
FROM   dbugxamc7km01t.i8qMCzNt9_posts ii
WHERE  ia.rowid = ii.rowid
AND    ia.enabled IS NOT FALSE
AND    ia.post_type="page";

Can anyone help point out where I am going wrong? I did used to do quite a bit of SQL but seriously out of touch now.

Kind regards,

John

P.S. I was asked to * Please format your code. Just highlight it and press Control-K. but that didn't work, just opened a search. So apologies if the formatting is off

2

Welcome

Is this an issue with microsoft sql server or another type of product?

3

Looks like MySQL.

4

It is MySQL in phpMyAdmin

5

do you mind sharing the syntax error? or would you like us to guess

6

My apologies, the first is:

#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 'WHERE id IN
(SELECT id FROM dbugxamc7km01t.i8qMCzNt9_posts WHERE `post_type`="p' at line 2

My second is:

#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 'FROM   dbugxamc7km01t.i8qMCzNt9_posts ii
WHERE  ia.rowid = ii.rowid
AND    ia.' at line 3