How to insert if not exists with selecting from same table?

Question

I have my table schema in H2 db as follows:

create table if not exists Test ( id bigint not null,name varchar(255), primary key (id) );

alter table Test add constraint if not exists Test_NAME UNIQUE (name);

I want to insert a value for the name attribute as 'Default' if it does not exist in the table by selecting the latest id value from the table and increment it by one.

Example: Do not insert if an entry for name = Default already exists.

ID | Name

1 | Default

Insert if an entry for name = Default does not exists.

ID | Name

1 | ABC

2 | XYZ

For the id column, find the max id and increment it by one. In this case, insert id=3 and name=Default.

My query is as follows:

INSERT INTO Test (id , name) SELECT max(id) + 1, 'Default' from Test WHERE NOT EXISTS (SELECT * FROM Test where name='Default');

However, it gives me an error saying: NULL not allowed for column "ID"; SQL statement as it applies the where condition on the inner select statement.

I also tried: MERGE INTO Test KEY(name) VALUES (SELECT MAX(id) + 1 from Test, 'Default');

It gives an error because, merge tries to update with the new values. If it finds 'Default', it will update the row with new id causing primary key violation.

Is there a better way to do this? How can I make the query work?

Answer 1

You are massively overcomplicating this. Define the id field as auto increment and place a unique index on the name field. The unique index prevents duplicate names to be inserted, while the auto increment increases the value of the id field by 1 (by default) if the insert is successful.

Answer 2

I updated id to auto increment and the following query work flawlessly

INSERT INTO Test (name) select * from (select 'Default') as tmp WHERE NOT EXISTS (SELECT name from Test where name='Default');

Answer 3

when you run your query first time, no record found in table so, it give error 'null' there, so if you add IFNULL() function there as below

INSERT INTO Test (id , name) 
SELECT **IFNULL**(max(id),0) + 1, 'Default' 
FROM Test 
WHERE NOT EXISTS (SELECT * FROM Test where name='Default');