Execute mysql insert only if table is empty

Question

Following this question, I've been trying to mimic the correct answer to insert data in my table only if it is empty.

This is my code:

INSERT INTO `statuses` SELECT 
    ('Something', 'Something else', 123),
    ('Something', 'Something else', 234)
    WHERE NOT EXISTS (SELECT * FROM `statuses`);

But instead of adding the elements if the table is empty, I just get:

#1064 - 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 * FROM `statuses`)'

How exactly am I supposed to write the query so that the values are inserted only if the table is empty?

First of all it should be VALUES instead of the first SELECT. — Sebastian, Mar 25 '13 at 00:51
the top answer in the linked question says differently. I was trying to copy the code posted: it tells not to use VALUES. — Saturnix, Mar 25 '13 at 00:52

Gordon Linoff · Accepted Answer · 2013-03-25T01:24:48.790

3

You have a syntax error in your statement:

INSERT INTO `statuses`
    (SELECT  'Something', 'Something else', 123
     WHERE NOT EXISTS (SELECT * FROM `statuses`)
    ) union all
    (SELECT 'Something', 'Something else', 234
     WHERE NOT EXISTS (SELECT * FROM `statuses`)
    );

You need to repeat the where twice in this case, once for each subquery. You can also do:

INSERT INTO `statuses`
    select t.*
    from ((SELECT  'Something' as col1, 'Something else' as col2, 123 as col3
          ) union all
          (SELECT 'Something', 'Something else', 234
          )
         ) t
    WHERE NOT EXISTS (SELECT * FROM `statuses`);

In this version, you need to assign names to the columns.

Or, you could just use two separate insert statements.

edited Mar 25 '13 at 01:24

answered Mar 25 '13 at 01:06

Gordon Linoff

1,122,135
50
484
624

the first one is giving me this error: "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 * FROM `statuses`) union all SELECT 'Something', '' at line 3" – Saturnix Mar 25 '13 at 01:13
@Saturnix ... The only thing I can think of is a parsing problem. I added parentheses to prevent that. In practice, I usually put `union` statements in the `from` clause, as in the second query. – Gordon Linoff Mar 25 '13 at 01:26
the first one still doesn't work (same error as before) but the second works fine, thanks! – Saturnix Mar 25 '13 at 01:32

score 1 · Answer 2 · edited Aug 21 '14 at 09:47

1

correct syntax is:

INSERT INTO user_authentication
SELECT t.*
FROM ((SELECT 1 as col1, 'kingston' as col2, 'test' as col3, true as col4)) t
WHERE NOT EXISTS (SELECT * FROM user_authentication);

This is working for me.

edited Aug 21 '14 at 09:47

Sven Rojek

4,560
2
32
53

answered Aug 21 '14 at 09:34

Kingston

11
1

Execute mysql insert only if table is empty

2 Answers2

Linked