CODEWITHSUNDEEP
mysqldatabaseh2insert-query
LARKINS CARVALHO
LARKINS CARVALHO

Reputation: 68

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

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?

Upvotes: 0

Views: 2706

Answers (3)

Janak Vanani
Janak Vanani

Reputation: 11

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');

Upvotes: 0

LARKINS CARVALHO
LARKINS CARVALHO

Reputation: 68

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');

Upvotes: 0

Shadow
Shadow

Reputation: 34294

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.

Upvotes: 3

Related Questions