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.

Answer 1

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 ... )