Mysql Foreach Loop Syntax

Question

I have a table 'posts' that has all info about my posts. I want to insert a new row into a different table for all posts that are of type 'public'. I want to run a query that would look something like this:

$ids = (SELECT post_id FROM posts WHERE post_type = 'public')    
foreach($id in $ids){
    INSERT INTO new_posts (post_id, post_data) VALUES ($id, 'hello');
}

I know I can insert multiple rows in one sql statement, but I need the post_id to change for each one and there are too many for me to change it manually.

What is the correct mysql syntax for this that I can run in my database manager? (Adminer)

juergen d · Accepted Answer · 2013-08-06 19:23:28Z

11

You don't need a loop. You can do it in one query

INSERT INTO new_posts (post_id, post_data)
SELECT post_id, 'hello'
FROM posts 
WHERE post_type = 'public'

answered Aug 6, 2013 at 19:23

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2306941 Over a year ago

Thanks! Worked like a charm!

Collectives™ on Stack Overflow

Mysql Foreach Loop Syntax

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related