How to use a single query for sql update or insert?

393 Asked by Dadhijaraj in SQL Server , Asked on Sep 29, 2022

I have a table test having columns id which primary key and auto incremented and name. I want to insert a new record if and only if there are no records. For example

input is id=30122 and name =john

if there are records with id 30122 then I have updated the name column to john,if there are no records then I have inserted a new record.

I can do using 2 queries like

select * from test where id=30122

if it has some records then I can use update test set name='john' where id=3012

or if it does not have records then I can use

insert into test(name) values('john')

But I wanted to use a single query?

Can somebody tell if it's possible?

Answered by Damini das

For sql update or insert -

You can try this

IF EXISTS(select * from test where id=30122)

update test set name='john' where id=3012

ELSE

insert into test(name) values('john');

Other approach for better performance is

update test set name='john' where id=3012

IF @@ROWCOUNT=0

insert into test(name) values('john');

and also read this bad habits to kick on schema prefix

How to use a single query for sql update or insert?

Your Answer