Erroring Mysql insert query with select (works in Postgres/H2)

Question

I'm trying to make some SQL work with MySQL and am having trouble with this insert statement:

INSERT INTO properties (fkid, name, value)
    SELECT 3, 'foo', 'bar'
    WHERE NOT EXISTS (
        SELECT 1 FROM properties WHERE fkid = 3 AND name = 'foo' 
    )

It's just supposed to no-op if the fkid/name already exists, and I want something that also works with Postgres. I'm now convinced there's something stupid I cannot see.

For what it's worth, the error is:

You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near
'WHERE NOT EXISTS (SELECT 1 FROM vertex_properties WHERE vertex_id = 4 AND name ='

From other examples I can see here on this site, it should work. Please can someone put me out of my misery.

Edit: to clarify, I want a cross-platform (Postgres/MySQL) "insert if not exists" solution. INSERT IGNORE ... only works for MySQL, the INSERT ... SELECT WHERE NOT EXISTS ... only seems to work for Postgres.

score 0 · Answer 1 · answered Jul 03 '13 at 18:37

0

You need FROM clause, because you are using WHERE. WHERE needs to know in which table something shouldn't exist.

INSERT INTO properties (fkid, name, value)
SELECT 3, 'foo', 'bar'
FROM properties
WHERE NOT EXISTS (
    SELECT 1 FROM properties WHERE fkid = 3 AND name = 'foo' 
)

answered Jul 03 '13 at 18:37

ikonic

71
4

From what I can see this, this always fails to insert if the properties table is initially empty. In this case, the fkid and name definitely won't be matched and the insert should succeed. – Mikesname Jul 04 '13 at 09:57

Erroring Mysql insert query with select (works in Postgres/H2)

1 Answers1