CODEWITHSUNDEEP
sqloracle-database
user1134858
user1134858

Reputation: 13

SQL: How can I insert a row if table is empty, otherwise do nothing?

I have a table that always has 0 or 1 rows. I want to write a script that will insert a row if it is empty, and do nothing if there is a row. I tried this:

SELECT * CASE WHEN (SELECT COUNT(*) FROM table < 0)
   THEN (INSERT INTO table (a, b, c, d) VALUES ('a', 'b', 'c', 'd'))
   END CASE
FROM table;

But I get:

FROM keyword not found where expected.

This seems like it should be pretty simple what am i doing wrong?

Upvotes: 1

Views: 4877

Answers (2)

EvilTeach
EvilTeach

Reputation: 28837

Use the MERGE statement with an INSERT but no UPDATE

Upvotes: 0

Factor Mystic
Factor Mystic

Reputation: 26770

You could restructure the query into a select/insert and use the not exists keywords to check if the table is empty, like so:

insert table (a, b, c, d)
select 'a', 'b', 'c', 'd'
from dual
where not exists (select 1 from table)

or, if it needs to be an if statement, similarly:

if not exists (select 1 from table)
    insert table (a, b, c, d) values ('a', 'b', 'c', 'd')

Upvotes: 13

Related Questions