How do you do multiple inserts that have selects

Question

How do I do an insert multiple values or records that have to get their information from select statements. This doesn't work.

INSERT INTO marriedcouples (male,female) VALUES ( (SELECT id FROM men WHERE username='brad', SELECT id FROM women WHERE username='jennifer') (SELECT id FROM men WHERE username='ken', SELECT id FROM women WHERE username='barbie'))

Assuming I have tables with:

men(id,name), women(id,name), couples(id,male,female)

etc.

Thanks, Dan

Thomas · Accepted Answer · 2011-05-13 06:06:47Z

Insert marriedcouples( male, female ) Select M.id, W.id From Men As M Cross Join Women As W Where ( M.username = 'brad' And W.username = 'jennifer' ) Or ( M.username = 'ken' And W.username = 'barbie' )

Addition

In comments, you asked specifically about the problems with your original query. First, you could have used your original approach like so:

Insert marriedcouples( male, female ) Select ( Select Id From men Where username = 'brad' ) , ( Select Id From women Where username = 'jennifer' ) Union All Select ( Select Id From men Where username = 'ken' ) , ( Select Id From women Where username = 'barbie' )

Notice that each value is enclosed in parentheses as its own encapsulated subquery. Second, notice that I used the Union All directive to allow me to stack the two queries and give me two rows. Third, notice that I'm not trying to use the Values directive in combination with subqueries. You can use the Values clause to list out values or you can use a Select statement but not both in the way you did. Obviously, this approach of four subqueries will not perform well but it helps to understand the breakdown of the syntax.

hi daniel savage,this solution seems to work for you by Thomas
I'm sure your answer will work but I was wondering if someone could explain why my original query structure did not work. Or maybe provide an example without joins and aliases. I'm going to be outputing the query dynamically from actionscript in a loop so I need something with a simple structure. In my example above the the individual selects and inserts work by themselves until I combine them.
@daniel savage - Two problems. First, if you are going to use Select statements with your Insert statement, you need to use that instead of the Values clause (i.e. do not try to use both at the same time). Second, you would need to Union (or Union All) the two pairs of Select statements so that you get two rows.
@daniel savage - I expanded my answer to give you a bit more info about the problems in your original approach.
@Thomas so I can use only one Select clause only with an insert, and there is no way to do multiples. But there is a way to insert multiple values but they are not compatible to use with Selects? I don't understand Joins all that well yet. I'm having trouble visualizing how joining men and women allows the insert to work. Is there a less elegant but easier to understand way of writing this?

Collectives™ on Stack Overflow

How do you do multiple inserts that have selects

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related