INSERT multiple rows with a subselect? (PostgreSQL)
Posted on 2002-03-22
Ok, I am not sure if it works like this, but it's worth a try.
I have three tables, a hypothetical example with the same relationships would be:
book_id (referring to a book from the table above)
what I need, is the quickest way, given a book_id a user name and a comment to add a row to the notes table for each page associated with that book_id, with the same comment and user (I know it doesn't make much sense in this example, just trust me, it's what I need)
to help visualize it (let's say we are doing this for book 3, the comment is "good book" and the user name is 'billg'):I
NSERT INTO notes VALUES ((SELECT page_id FROM books WHERE book_id = 3),'good book', 'billg')
now this obviously doesn't work, but if it did, it would create as many rows in notes as there are pages with book_id = 3 and each one would have the same user and comment.
Is there a way to do this in one statement? (I am hopeful, but doubtful)
Oh, as the title says, I am using postgres