CODEWITHSUNDEEP
mysqlsql
user291701
user291701

Reputation: 39691

Execute INSERT if table is empty?

Is there a way to do an insert under a count condition, something like:

INSERT INTO my_table (colname) VALUES('foo') IF COUNT(my_table) < 1

Basically I want to insert a single default record if the table is currently empty. I'm using mysql.

Upvotes: 27

Views: 38978

Answers (3)

joehep
joehep

Reputation: 154

This is easier to read:

INSERT INTO my_table (colname) 
SELECT 'foo' FROM DUAL
WHERE NOT EXISTS (SELECT * FROM my_table);

The lack of a VALUES is mitigated by the SELECT FROM DUAL which will provide the values. the FROM DUAL is not always required, but it doesn't hurt to include it for that weird configurations where it is required (like the installation of Percona I am using).

The NOT EXISTS is faster than doing a count which can be slow on a table with a large number of rows.

Upvotes: 1

Jeff Swensen
Jeff Swensen

Reputation: 3573

One way would be to place a unique key on a column. Then execute a REPLACE:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted

Upvotes: 2

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

Use SELECT instead of VALUES to be able to expand the query with a WHERE clause.

EXISTS is a better & faster test than COUNT

INSERT INTO my_table (colname)
SELECT 'foo'
WHERE NOT EXISTS (SELECT * FROM my_table)

Upvotes: 58

Related Questions