MySQL IF statement to check if table is empty

Question

How can I create an IF statement that will insert a default row if a table is found to be empty? I'm following this logic but something is wrong.

IF ((SELECT * FROM myTable)=0)
THEN
INSERT INTO myTable
(myColumn) VALUES (myValue)
END IF;

#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

Answer from comments (McAdam331) http://sqlfiddle.com/#!9/42229/1

CREATE TABLE myTable(
  name VARCHAR(100));

INSERT INTO myTable (`name`)
SELECT 'namer'
FROM DUAL
WHERE NOT EXISTS (SELECT * FROM myTable);

Note that you can not use `if-else` in query unless its inside functions,procedures or triggers — Abhik Chakraborty, Apr 29 '15 at 13:44
Oh...... I was trying to do this in an .sql file that creates all the tables. — tdoakiiii, Apr 29 '15 at 13:45
Should any non-empty table be expected to have the default row's exact contents already? If so, you may be accomplish it with appropriate indexing and an `ON DUPLICATE KEY` expression. — Michael Berkowski, Apr 29 '15 at 13:45
It's a user table and if its empty it should create the default admin/admin row — tdoakiiii, Apr 29 '15 at 13:46

Ian Kenney · Answer 1 · 2015-04-29T14:33:23.313

0

Did you look at not exists ?

INSERT INTO table1(`name`) 
SELECT 'namer'  
WHERE NOT EXISTS ( SELECT * FROM table1 );

edited Apr 29 '15 at 14:33

answered Apr 29 '15 at 14:01

Ian Kenney

6,376
1
25
44

Yes, I get error #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 `table` – tdoakiiii Apr 29 '15 at 14:22
I don't know if it's true but I read "Use SELECT instead of VALUES to be able to expand the query with a WHERE clause." – tdoakiiii Apr 29 '15 at 14:24
Yeahp. Unfortunately I get the same error anyways. http://stackoverflow.com/questions/5307164/execute-insert-if-table-is-empty exact same code, don't understand why it doesn't work for me. I can run this query without the WHERE portion, but as soon as I add it i get the error. – tdoakiiii Apr 29 '15 at 14:27
post the sql you are trying now ? – Ian Kenney Apr 29 '15 at 14:28
INSERT INTO 'table1' ('name') SELECT ('namer') WHERE NOT EXISTS ( SELECT * FROM 'table1' ); – tdoakiiii Apr 29 '15 at 14:29
Is the issue because there is no FROM clause? Where are you selecting 'namer' from? – AdamMc331 Apr 29 '15 at 14:33
@McAdam331 I don't know, look at this 'correct' answer with the same code. http://stackoverflow.com/questions/5307164/execute-insert-if-table-is-empty – tdoakiiii Apr 29 '15 at 14:33
I think it is wrong quotes around table / field names and the brackets round the select field list – Ian Kenney Apr 29 '15 at 14:34
@tdoakiiii that works because 'namer' is a constant string, they want to add that string. Is that what you are trying to do, or are you trying to select that from a certain table? – AdamMc331 Apr 29 '15 at 14:35
No. In this Answer's context, I am just trying to insert the word "namer" into the column "name" if the table is empty. – tdoakiiii Apr 29 '15 at 14:36
1

@tdoakiiii and Ian, if you read the first comment on the answer, you need to have a dummy table in the FROM clause as I mentioned. See this SQL Fiddle for example: http://sqlfiddle.com/#!9/42229/1 – AdamMc331 Apr 29 '15 at 14:37
The `WHERE NOT EXISTS` part doesn't exist in mysql's `INSERT INTO ...` (it does in `SELECT ...` queries) but does apply to SQL-Server. http://dev.mysql.com/doc/refman/5.6/en/insert.html. – Benz Apr 29 '15 at 14:38
2

@Benz in this case, it's part of the SELECT clause. That's why FROM is also required, and the syntax will work so long as that from clause exists, see the fiddle I linked. – AdamMc331 Apr 29 '15 at 14:42
Thanks McAdam331 (and everyone else). That was completely harder than I expected. – tdoakiiii Apr 29 '15 at 14:46
1

@McAdam331 Thx for the answer, I was not aware that the `WHERE NOT EXISTS` part would automatically be a part of the SELECT.. Next to that I was not familiair with `FROM DUAL`, thanks! – Benz Apr 29 '15 at 14:48
Sorry to revive - Any way to extend this to insert more than one row? – tdoakiiii Apr 29 '15 at 15:48

score 0 · Answer 2 · answered Apr 29 '15 at 14:06

You can use something like this:

/* Insert query */
INSERT INTO myTable (
    /* You place the values in the 'FROM' part of this query */
    SELECT tbl.*
    FROM ( SELECT 1 AS id, 'username' AS username, 'password' AS password ) AS tbl
    WHERE 0 = (SELECT COUNT(*) FROM myTable )
);

It starts with an INSERT part. After that comes a subquery to select 'records' (which you can give values in the FROM ( SELECT ... part. The where makes sure that there are only inserts if no records currently exist.

score 0 · Answer 3 · answered Apr 29 '15 at 14:40

0

http://sqlfiddle.com/#!2/ba9ed/1

INSERT INTO table1 (myColumn)
  SELECT 
    'myValue' 
  FROM (
    SELECT COUNT(*) c 
    FROM table1 t
    HAVING c=0) t2;

answered Apr 29 '15 at 14:40

Alex

16,739
1
28
51

MySQL IF statement to check if table is empty

3 Answers3

Linked