CODEWITHSUNDEEP
mysqlsqlmariadb
rock11
rock11

Reputation: 758

Only insert into table if it is empty

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

Upvotes: 3

Views: 2464

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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.

Upvotes: 4

Amirhossein
Amirhossein

Reputation: 1251

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

Upvotes: 0

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

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

Upvotes: 2

Apoorv Mishra
Apoorv Mishra

Reputation: 1649

You can use exists like:

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

Upvotes: 0

Related Questions