Insert into with multiple subqueries as values

Question

Lets suppose I've to insert into a table with many fk, just to explain here below the wrong statement:

insert into mytable
values
(
somevalue
,somevalue
,select id from othertable1 where ...condition
,select id from othertable2 where ...condition
,select id from othertable3 where ...condition
)

so basically values to insert comes from different subqueries, is it possible to achieve such a behavior ?

Triple Gilaman · Accepted Answer · 2012-06-05 16:36:40Z

18

insert into mytable (columns)
select somevalue, somevalue, a.id, b.id, c.id
from
 othertable1 a
 cross join othertable2 b
 cross join othertable3 c
where
 a ... condition
 b ... condition
 c ... condition

answered Jun 5, 2012 at 16:36

Triple Gilaman

4833 silver badges10 bronze badges

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

1 Comment

VansFannel Over a year ago

I have the same problem but with only two values and othertable1 and othertable2 are the same table: stackoverflow.com/questions/49025589/…

Russell Fox · Accepted Answer · 2012-06-05 16:39:58Z

1

Can you use a select statement to do the insert?

INSERT INTO MYTABLE
SELECT (SOMEVALUE,
    SOMEVALUE,
    T1.ID,
    T2.ID
)
FROM ANOTHERTABLE T1
JOIN YETANOTHERTABLE T2
    ON T1.BLAH = T2.BLAH
WHERE condition1...

answered Jun 5, 2012 at 16:39

Russell Fox

5,4651 gold badge26 silver badges28 bronze badges

1 Comment

Felice Pollano Over a year ago

I can't use JOIN since ti is not correlated with T2, the cross join was the missing point for me.

Collectives™ on Stack Overflow

Insert into with multiple subqueries as values

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related