MySQL "IF NOT EXISTS"

Question

I am having error in SQL syntax but I don't see it.

$query        = "IF NOT EXISTS ( SELECT id FROM Provider 
                 WHERE name=('$filename') )
                 INSERT INTO Provider (Name) VALUES ('$filename')";

$query_result = mysqli_query($connect, $query);

The code

INSERT INTO Provider (Name) VALUES ('$filename')

worked normally if I use just that.

Also the code

SELECT id FROM Provider WHERE name=('$filename')

worked fine when I tested its value

When I added IF NOT EXISTS I have SQL syntax error but I can't see it! Any ideas?

You have syntax error, because you simply can't do that. – Jakub Matczak Sep 07 '17 at 07:07 — Jakub Matczak, Sep 07 '17 at 07:07
Whats "if not exists" for you? – SilverSurfer Sep 07 '17 at 07:08 — SilverSurfer, Sep 07 '17 at 07:08

score 1 · Accepted Answer · answered Sep 07 '17 at 07:08

1

You cannot use NOT EXISTS in this context. Try the following query instead:

INSERT INTO Provider (Name) 
SELECT filename
FROM (SELECT ('$filename') AS filename) AS t
WHERE NOT EXISTS (SELECT id 
                  FROM Provider 
                  WHERE name=('$filename')

answered Sep 07 '17 at 07:08

Giorgos Betsos

68,064
7
48
83

I have logical error. I see now, actually I don't need to SELECT the id. I have table Providers, and the filename is provider name, I just want to check if the name is already in the table, if it is not, then to put the record, if not, just to skip. In my case I want to ask if the file name exists in the table already. – Milos Sep 07 '17 at 07:38
The field of the `SELECT` clause of a `NOT EXISTS` operator, `id` in this case doesn't play any role. You could have used `SELECT 1` instead. – Giorgos Betsos Sep 07 '17 at 07:41

MySQL "IF NOT EXISTS"

1 Answers1