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?

Vatev
– Vatev

2018-04-20 13:19:41 +00:00
Commented Apr 20, 2018 at 13:19 — Vatev
– Vatev, Commented Apr 20, 2018 at 13:19

Jay Shankar Gupta · Accepted Answer · 2018-04-20 12:54:38Z

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, 2018 at 12:54

answered Apr 20, 2018 at 12:48

Jay Shankar Gupta

6,1281 gold badge15 silver badges31 bronze badges

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

7 Comments

user9674353 Over a year ago

what if I have 3 rows to add?

Jay Shankar Gupta Over a year ago

Check I Edited My answer

user9674353 Over a year ago

I get this: Error Code: 1267. Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '='

user9674353 Over a year ago

I'm using Mysql 5.7.21

Jay Shankar Gupta Over a year ago

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;

|

Collectives™ on Stack Overflow

MYSQL - Insert If Table is empty

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related