combination of "WHERE NOT EXISTS" & "SELECT 1 FROM" not working

Question

I've an query working in SQLite but it's not working in MySQL.

I'm using: PHP, PDO, SQLite & MySQL

Here is the query:

INSERT INTO mytable (id, name, tag, timestamp)
    SELECT 11, 'Legio', 'LR', 1234567
        WHERE NOT EXISTS (
            SELECT 1 FROM mytable 
                WHERE id = 11 AND name = 'Legio' AND tag = 'LR' AND timestamp = (
                    SELECT max(timestamp) FROM mytable WHERE id = 11))

The result is an error message:

Syntax error near 'WHERE NOT EXISTS ( SELECT 1 FROM mytable WHERE id = 11 AND name' at line 3.

What is wrong with this query in MySQL?

Is it possible to adapt this statement to work in MySQL and SQLite???

Answer 1

You need to select from a table name when using a WHERE clause. Since you're not actually selecting from a real table, you can use the special table name DUAL

INSERT INTO mytable (id, name, tag, timestamp)
SELECT 11, 'Legio', 'LR', 1234567
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM mytable 
    WHERE id = 11 AND name = 'Legio' AND tag = 'LR' 
        AND timestamp = (
            SELECT max(timestamp) FROM mytable WHERE id = 11))

Answer 2

It should be:

SELECT 11, 'Legio', 'LR', 1234567 FROM `table_name` WHERE NOT EXISTS...

You missed the FROM.