mysql - INSERT SELECT WHERE NOT EXISTS on empty table

Question

So I have the following example query

INSERT INTO company (name)
SELECT 'test'
FROM company
WHERE
  NOT EXISTS (SELECT 'test' FROM company WHERE name = 'test');

However this does not work when the table is empty.

I thought something like this would work

INSERT INTO company (name)
SELECT 'test'
FROM company
WHERE
  (SELECT COUNT(*) FROM company) = 0
  OR
  NOT EXISTS (SELECT 'test' FROM company WHERE name = 'test');

But it didn't work either. Any ideas?

http://sqlfiddle.com/#!9/5747f0/1

Please update your question and describe what you actually want to happen here. — Tim Biegeleisen, Sep 03 '17 at 06:59
@TimBiegeleisen I'm not sure how to describe it any clearer. It should insert if the name doesn't exist already (which already works once the table is filled) or if the table is empty (current query does not work if table is empty). So the only bug is fixing it when the table is empty. — A. L, Sep 03 '17 at 07:01
I can't seem to reproduce your example ([see here](http://rextester.com/SJN88611)). Maybe you should give a reproducible example. — Tim Biegeleisen, Sep 03 '17 at 07:12

score 4 · Accepted Answer · answered Sep 03 '17 at 07:33

Look closely at the following query:

INSERT INTO company (name)
SELECT 'test'
FROM company
WHERE
    NOT EXISTS (SELECT 'test' FROM company WHERE name = 'test');

Since the company table is currently empty, no records will ever be returned from this query, regardless of the WHERE EXISTS logic, which by the way is completely correct. If you instead use the dual table you will see the query working:

INSERT INTO company (name)
SELECT 'test'
FROM dual
WHERE
     NOT EXISTS (SELECT 'test' FROM company WHERE name = 'test');

What does `'test'` represent? I _think_ I need the same query for my solution but not clear with just a query for an answer without an explanation behind it. — HPWD, Aug 07 '18 at 19:35

score 1 · Answer 2 · answered Sep 03 '17 at 07:34

No suprise that this has come up before prevent autoincrement on MYSQL duplicate insert

I like this solution

INSERT INTO COMPANY (NAME) SELECT 'TEST' AS INNAME FROM DUAL
WHERE NOT EXISTS(
    SELECT NAME FROM COMPANY
    WHERE NAME = 'TEST'
    LIMIT 1
);

MariaDB [sandbox]> DROP TABLE IF EXISTS COMPANY;
Query OK, 0 rows affected (0.12 sec)

MariaDB [sandbox]> CREATE TABLE COMPANY (ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(10), UNIQUE (NAME));
Query OK, 0 rows affected (0.24 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> INSERT INTO COMPANY (NAME) VALUES ('TEST');
Query OK, 1 row affected (0.08 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> INSERT INTO COMPANY (NAME) SELECT 'TEST' AS INNAME FROM DUAL
    -> WHERE NOT EXISTS(
    ->     SELECT NAME FROM COMPANY
    ->     WHERE NAME = 'TEST'
    ->     LIMIT 1
    -> );
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> INSERT INTO COMPANY (NAME) SELECT 'ABC' AS INNAME FROM DUAL
    -> WHERE NOT EXISTS(
    ->     SELECT NAME FROM COMPANY
    ->     WHERE NAME = 'ABC'
    ->     LIMIT 1
    -> );
Query OK, 1 row affected (0.10 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> SELECT * FROM COMPANY;
+----+------+
| ID | NAME |
+----+------+
|  2 | ABC  |
|  1 | TEST |
+----+------+
2 rows in set (0.00 sec)

score 0 · Answer 3 · answered Sep 03 '17 at 06:52

0

It looks like you're reinventing the wheel here. A much simpler approach would be to define name as a unique key (or primary key) and then use the insert ignore syntax:

INSERT IGNORE INTO company(name) VALUES ('test')

answered Sep 03 '17 at 06:52

Mureinik

297,002
52
306
350

Reason why I don't want to `INSERT IGNORE` is because I don't want the id to keep incrementing every time someone tries to insert the same name. – A. L Sep 03 '17 at 06:53
Then your current query would fail to do that...the insert would be blocked and nothing would increment. – Tim Biegeleisen Sep 03 '17 at 06:54
@TimBiegeleisen Not sure what you mean, but when I run the original query (and there's already something in the database) the id will not increment if the same name is in there already – A. L Sep 03 '17 at 06:56

mysql - INSERT SELECT WHERE NOT EXISTS on empty table

3 Answers3