CODEWITHSUNDEEP
mysqlsqlsubqueryinsert-into
Wolf
Wolf

Reputation: 1001

SQL Insert Into multiple rows, but execute subquery only once

Im trying to optimize an INSERT INTO statement to use a subquery only once, as it is always the same value:

Heres my example code

INSERT INTO TABLE1 (id, number) VALUES 
((SELECT other_id from TABLE2 WHERE somevalue = "test"), 12),
((SELECT other_id from TABLE2 WHERE somevalue = "test"), 13),
...,
...;

Not an sql-expert, but this doesnt look like a good approach, as the same subquery gets executed on every insert.

Is there an alternative solution?

also, i know i can select the ID beforehand and store it in a variable like this (pseudo-code-like):

$var = mysql_query("SELECT other_id from TABLE2 WHERE somevalue = 'test'")
 mysql_query("INSERT INTO TABLE1 (id, number) VALUES 
    ($var, 12),
    ($var, 13);")

Upvotes: 1

Views: 273

Answers (1)

Quassnoi
Quassnoi

Reputation: 425391

INSERT
INTO    table1 (id, number)
SELECT  other_id, number
FROM    table2
CROSS JOIN
        (
        SELECT  12 number
        UNION ALL
        SELECT  13
        ) q
WHERE   somevalue = 'test'

Upvotes: 2

Related Questions