Question 228810 in Wordpress Question posted in
The official Wordpress documentation can be found here.

MySQL for wordpress database; got 10,000 post_IDs from postmeta table, need to insert 10,000 entries back into postmeta table. How?

I’ve made many attempts at this problem, using a variety of approaches, and just haven’t been able to solve it just yet. Any SQL experts?

This query returns around 10,0000 post_ids:

SELECT post_id
FROM `Ug77XZe58_postmeta`  
WHERE meta_key LIKE "%_wc_gla_sync_status%";

I need to insert back into:

`Ug77XZe58_postmeta` (meta_id[AUTOINC], post_id, meta_key, meta_value)

(post_id, '_wc_gla_brand', 'TheYayAtelier')

So that every product in the woocommerce database, and their variants, gets assigned the brandname "TheYayAtelier". Google Shopping Listings is complaining about their being no brands on the products.

I’ve tried

INSERT INTO `Ug77XZe58_postmeta` (post_id,meta_key,meta_value)
    SELECT post_id, '_wc_gla_brand', 'TheYayAtelier'
    FROM `Ug77XZe58_postmeta`
    WHERE post_id IN
(SELECT post_id
FROM `Ug77XZe58_postmeta`  
WHERE meta_key LIKE "%_wc_gla_sync_status%"
);

But this makes 290,000 insertions instead of the expected 10,000. Something’s wrong.

I then tried

INSERT INTO `Ug77XZe58_postmeta` (post_id,meta_key,meta_value) 
VALUES(
(SELECT post_id
FROM `Ug77XZe58_postmeta`  
WHERE meta_key LIKE "%_wc_gla_sync_status%"), 
'_wc_gla_brand', 'TheYayAtelier');

But this complains about the source and target table being the same. Not sure what I should be doing. Seems straightforward, with 10,000 post_ids, make 10,000 simple insertions using each post_id once, but coming up with the MySQL to do it is proving challenging.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    How can I insert values into a table, using a subquery with more than one result?

    Following the top answer to this question, I came up with:

    insert into `Ug77XZe58_postmeta` (post_id,meta_key,meta_value)
select  post_ID, '_wc_gla_brand', 'TheYayAtelier'
from `Ug77XZe58_postmeta` 
WHERE meta_key LIKE "%_wc_gla_sync_status%";

    And this inserted the expected 10,000 rows. Random product was verified to have the brandname "TheYayAtelier" according to the woocommerce GUI. Appears to work.


  2. 0

    Do you actually need to check for that key? Simple solution would be this:

    INSERT INTO Ug77XZe58_postmeta ( post_id, meta_key, meta_value )
SELECT Ug77XZe58_posts.id, '_wc_gla_brand', 'TheYayAtelier' FROM Ug77XZe58_posts;

    This query would add one row for every row in Ug77XZe58_posts, to Ug77XZe58_postmeta. You can see how dynamic values are used, and can change the rest on your own. Since meta_id

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search