Only insert into table if it is empty

Question

I want to insert a row to the table if only the table is empty. I went through this Execute INSERT if table is empty? but am unable to do so.i am using MariaDB as rdbms.Please help.Thanks in advance.

Here is my sql syntax:

INSERT INTO `policy` (`policy1`, `policy2`, `policy3`, `policy4`)
    SELECT ('F', 'F', 'F', 'F')
    WHERE NOT EXISTS (SELECT * FROM `policy`)

My table structure is:

CREATE TABLE `policy` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `policy1` varchar(255) DEFAULT NULL,
  `policy2` varchar(255) DEFAULT NULL,
  `policy3` varchar(255) DEFAULT NULL,
  `policy4` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

I tried by removing the parentheses from select but I still get sql error in workbench — rock11, Apr 23 '19 at 10:11
Offtopic: Incrementing column names like `policy1`, `policy2` and so on are most likely also good candidate for normalisation.. — Raymond Nijland, Apr 23 '19 at 10:22
@RaymondNijland these are just dummy column names.They'll change later . Good point though. — rock11, Apr 23 '19 at 10:27

score 4 · Answer 1 · answered Apr 23 '19 at 10:39

4

You can also use the dual table for this purpose:

INSERT INTO `policy` (`policy1`, `policy2`, `policy3`, `policy4`)
    SELECT 'F', 'F', 'F', 'F'  -- no parentheses!
    FROM dual
    WHERE NOT EXISTS (SELECT * FROM `policy`) ;

dual is a built-in table with one row, designed for purposes like this. The naming and idea come from the Oracle database.

answered Apr 23 '19 at 10:39

Gordon Linoff

1,242,037
58
646
786

thank you for this hint! I searched for a reference why there is a difference between mysql and mariadb. I found this: *FROM DUAL could be used when you only SELECT computed values, but require a WHERE clause, perhaps to test that a script correctly handles empty resultsets* - https://mariadb.com/kb/en/library/dual/ – Sebastian Brosch Apr 23 '19 at 10:50

score 2 · Accepted Answer · answered Apr 23 '19 at 10:15

2

You can use the following solution:

INSERT INTO `policy` (`policy1`, `policy2`, `policy3`, `policy4`)
    SELECT * FROM (SELECT 'F' c1, 'F' c2, 'F' c3, 'F' c4)t
    WHERE NOT EXISTS (SELECT 1 FROM `policy`);

demo on dbfiddle.uk

answered Apr 23 '19 at 10:15

Sebastian Brosch

42,106
15
72
87

score 0 · Answer 3 · answered Apr 23 '19 at 10:12

0

You can use exists like:

if not exists(select 1 from table)
begin
    -- do stuff
end

answered Apr 23 '19 at 10:12

Apoorv Mishra

1,489
1
8
12

1

no you can't this is not SQL Server (MSSQL) where this syntax is allowed on plain SQL exection.. In MySQL this syntax is only allowed on stored programs like triggers, functions and procedures.. So this syntax wil give a parse error on MySQL and MariaDB. – Raymond Nijland Apr 23 '19 at 10:18

score 0 · Answer 4 · answered Apr 23 '19 at 10:37

0

INSERT INTO `policy` (`policy1`, `policy2`, `policy3`, `policy4`)
    Select * from (
    SELECT ('F', 'F', 'F', 'F')
    WHERE NOT EXISTS (SELECT 'F', 'F', 'F', 'F' FROM `policy`))

answered Apr 23 '19 at 10:37

Amirhossein

1,148
3
15
34

Only insert into table if it is empty

4 Answers4