CODEWITHSUNDEEP
mysql
Saturnix
Saturnix

Reputation: 10564

Execute mysql insert only if table is empty

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

Execute INSERT if table 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?

Upvotes: 4

Views: 5526

Answers (2)

Kingston
Kingston

Reputation: 11

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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.

Upvotes: 3

Related Questions