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
– Tim Biegeleisen, Commented Sep 3, 2017 at 6: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
– A. L, Commented Sep 3, 2017 at 7:01
I can't seem to reproduce your example (see here). Maybe you should give a reproducible example. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 3, 2017 at 7:12

Tim Biegeleisen · Accepted Answer · 2017-09-03 07:33:29Z

4

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');

answered Sep 3, 2017 at 7:33

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HPWD Over a year ago

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.

P.Salmon · Accepted Answer · 2017-09-03 07:34:45Z

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)

Mureinik · Accepted Answer · 2017-09-03 06:52:11Z

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 3, 2017 at 6:52

Mureinik

316k54 gold badges400 silver badges405 bronze badges

3 Comments

A. L Over a year ago

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.

Tim Biegeleisen Over a year ago

Then your current query would fail to do that...the insert would be blocked and nothing would increment.

A. L Over a year ago

@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

Collectives™ on Stack Overflow

mysql - INSERT SELECT WHERE NOT EXISTS on empty table

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related