MYSQL - Insert If Table is empty

Question

I want to insert some data on my table if the table is empty (0 rows).

I tried this:

IF NOT EXISTS(SELECT 1 FROM `account_types`)
THEN
       INSERT INTO `account_types` (`type`, `group`) VALUES ('400', 'test');
       INSERT INTO `account_types` (`type`, `group`) VALUES ('401', 'test2');
END IF;

But I get

Error Code: 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 'IF NOT EXISTS(SELECT 1 FROM account_types) THEN INSERT INTO `account_types' at line 1

What can I do ? I'm using MYSQL

Possible duplicate of [Execute INSERT if table is empty?](https://stackoverflow.com/questions/5307164/execute-insert-if-table-is-empty) — Vatev, Apr 20 '18 at 13:19

Jay Shankar Gupta · Answer 1 · 2018-04-20T12:54:38.560

1

INSERT INTO `account_types`(`type`, `group`)
SELECT * FROM
(SELECT '400' `type`, 'test' `group` UNION ALL
 SELECT '401' `type`, 'test2' `group` UNION ALL
 SELECT '402' `type`, 'test3' `group`) A
WHERE NOT EXISTS (SELECT NULL FROM account_types B WHERE A.type=B.type);

Demo

http://sqlfiddle.com/#!9/d1e80e/1

edited Apr 20 '18 at 12:54

answered Apr 20 '18 at 12:48

Jay Shankar Gupta

5,707
1
7
26

what if I have 3 rows to add? – Apr 20 '18 at 12:50
Check I Edited My answer – Jay Shankar Gupta Apr 20 '18 at 12:52
I get this: Error Code: 1267. Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '=' – Apr 20 '18 at 12:56
I'm using Mysql 5.7.21 – Apr 20 '18 at 12:56
Run Below Query and then try `SET collation_connection = 'utf8_general_ci';` `ALTER TABLE account_types CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;` – Jay Shankar Gupta Apr 20 '18 at 13:03
surelly there must be a better way to check if a table is empty and if it is add stuff – Apr 20 '18 at 13:05
and your code doesn't work. if I add more selects it will add more stuff to the table even tho the table is not empty. – Apr 20 '18 at 13:14

MYSQL - Insert If Table is empty

1 Answers1

Linked