Insert multiple rows from select statement with variable mysql

0
votes

Here's the sql statement I tried for adding data from one table to another.

INSERT INTO wp_postmeta(`post_id`,`meta_key`,`meta_value`) VALUES((SELECT ID FROM wp_posts tb WHERE tb.post_type = 'support'),'category_order','0')

Basicaly I want to add rows to the wp_postmeta table. I want to add two static values, meta_key and meta_value. But I also want to add post_id which should be derived from the ID of each row in wp_posts. This doesn't seem to work when there are multiple rows returned from the SELECT statement.

Thanks for the help.

2
phpmysqlwordpress
SELECT ID, 'category_order', '0' FROM.... - Patrick Q
@PatrickQ this doesn't make sense. These fields do not all exist in the wp_posts table. Only ID does. - mpn
That's why you select them as strings, not as column names. - Patrick Q

2 Answers

2
votes

Try this instead

INSERT INTO wp_postmeta(post_id,meta_key,meta_value) SELECT ID, 'category_order','0' FROM wp_posts tb WHERE tb.post_type = 'support'

0
votes
INSERT INTO wp_postmeta(`post_id`,`meta_key`,`meta_value`) SELECT ID, 'category_order','0' FROM wp_posts tb WHERE tb.post_type = 'support'