Multiple row INSERT from a single SELECT result

7
April 15, 2019, at 11:00 PM

I am inserting multiple rows into a table, using the result from a subquery :

INSERT INTO `doc-file` (docId, fileId) VALUES
    ((SELECT id FROM documents WHERE slug = :slug LIMIT 1), :file1),
    ((SELECT id FROM documents WHERE slug = :slug LIMIT 1), :file2),
    ((SELECT id FROM documents WHERE slug = :slug LIMIT 1), :file3),

The result from the subquery would be the same for every row I want to insert, so it really only needs to be run once. Written like this, wouldn't the subquery be run multiple times? Is there a way to run this subquery only once, and still reuse its result into multiple rows ?

Answer 1

You can use a variable:

set @value = (SELECT id FROM documents WHERE slug = :slug LIMIT 1);
INSERT INTO `doc-file` (docId, fileId) VALUES
    (@value, :file1),
    (@value, :file2),
    (@value, :file3),
Answer 2

You could cross join the subquery and another subquery getting the files by UNION ALLing FROM-less SELECTs.

INSERT INTO `doc-file`
            (docId,
             fileId)
            SELECT x.id,
                   y.file
                   FROM (SELECT id
                                FROM documents
                                WHERE slug = :slug
                                LIMIT 1) x
                        CROSS JOIN (SELECT :file1 file
                                    UNION ALL
                                    SELECT :file2 file
                                    UNION ALL
                                    SELECT :file3 file) y;
Answer 3

you could unique files in separate temp table and join it on condition like 1=1 If you dont want to maintain separate table populate distinct values in Subquery so you will have separate tables, let says fileNames fileNames: file1 file2 file3

  INSERT INTO `doc-file` (docId, fileId) VALUES
        ( select a.id,f.fileID 
        (SELECT id FROM documents WHERE slug = :slug LIMIT 1) a 
        inner join filenames f on 1=1 
    ))
READ ALSO
Equivalent Query from MySQL to PostgreSQL

Equivalent Query from MySQL to PostgreSQL

I'm switching from MySQL to PostgreSQL and trying to add a UNIQUE INDEX

16
MySQL / MariaDB column value unique in different column

MySQL / MariaDB column value unique in different column

I have a database schema for user login data that stores an E-Mail address for each userNaturally, this E-Mail needs to be unique because each user should only have one account

12
MySQL duplicate entry when updating multiple rows

MySQL duplicate entry when updating multiple rows

I have a table of items where each item has an itemOrderitemOrder is part of the PRIMARY KEY

32
Make relation between two table ? ( error )

Make relation between two table ? ( error )

I have two tables , one of them this in image : name of table

44