Insert with select statement

Question

I have a query that I can't fully understand. I test it with mysql v.5.5 and getting nothing. here is the snippet:

INSERT INTO
            logs (
                f1,
                f2,
                f3,
                f4,
                f5,
                f6
            )
        SELECT
                'test',
                'done',
                'test',
                'test',
                'test',
                'test'
        FROM
            logs
        WHERE
            NOT EXISTS (
                SELECT * FROM
                        logs
            WHERE
                f1 = 'test' and
                f2 = 'done' and
                f3 = 'test' and
                f4 = 'test' and
                f5 = 'test' and
                f6 = 'test'
            )
        LIMIT 1

What I understood is that the fields from other tables can be selected and iserted, but what I don't understand is why not fields, but field values are selected. Was this available in former versions of MySQL ? Also.. I need an appropriate query to be ran in SQL Server 2008. Any explanations? Thanks.

score 1 · Accepted Answer · edited May 23 '17 at 12:28

This SQL inserts a row in your table logs with the values

 f1 = 'test' and
 f2 = 'done' and
 f3 = 'test' and
 f4 = 'test' and
 f5 = 'test' and
 f6 = 'test'

if that row with that values doesn't exist in the table.

INSERT INTO
        logs (
            f1,
            f2,
            f3,
            f4,
            f5,
            f6
        )
        -- Here we're specifying which value will have every field
    SELECT
            'test',
            'done',
            'test',
            'test',
            'test',
            'test'
    FROM
        logs
    WHERE
        -- Here we're looking into the table 'logs' for the row with that values.
        -- If we match the condition we'll insert into the table
        NOT EXISTS (
            SELECT * FROM
                    logs
        WHERE
            f1 = 'test' and
            f2 = 'done' and
            f3 = 'test' and
            f4 = 'test' and
            f5 = 'test' and
            f6 = 'test'
        )
    LIMIT 1

About the version, from MySQL 5.0 this is available: https://dev.mysql.com/doc/refman/5.0/en/insert-select.html, and even MySQL 4.1: http://dev.mysql.com/doc/refman/4.1/en/insert-select.html

About SQL, I'm sorry, never worked with it, but surely it'll have a similar sentence, you may look into this SO question: Insert into ... values ( SELECT ... FROM ... )

very cool explanation. BTW, I tried to extract just the select .. from (without where) part, and the results are that the values in the select statements are treated as column names.. is this ok? — Vit Kos, Mar 04 '14 at 08:21
If you extract just the select, you're not doing anything. A select of a string is just the string. If you want assign the value as a field, you may do `SELECT 'string' AS f1 `, this will give you a f1 field with 'string' as value — Federico J., Mar 04 '14 at 08:29
XD, Ok, try this: `SELECT 'perry' as f1, 'mason' as f2;` this will give you a row with two fields, f1 => perry and f2 => mason, if you added a FROM clause: `SELECT 'perry' as f1, 'mason' as f2 FROM logs;` it wouldn't matter, this will give you the same result because you're not filtering with a `WHERE` in the table you're looking for — Federico J., Mar 04 '14 at 08:39

Insert with select statement

1 Answers1