How to insert or update using single query?

Question

I have a table test having columns id which primary key and auto incremented and name. I want to insert a new record if annd 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 update the name column to john,if there are no records then I have insert 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 single query?

Can somebody tell if its possible?

• Aaron Bertrand: But I wanted to use single query? Why?
• SpringLearner: @AaronBertrand My back end is developed using java.So If I use 2 quries then I have to hit the DB 2 times.So if it can be done using a single query then why to use 2 queries
• Aaron Bertrand: Java doesn't support a stored procedure, or a single batch with two statements requiring only one hit to the database?
• Aaron Bertrand: @eaglei22 I would use the 2nd example in vijayp's answer below. I would still not choose MERGE in any version, even SQL Server 2019. Some background on that here.
• Skippy le Grand Gourou: FWIW, the MySQL/MariaDB equivalent is ON DUPLICATE KEY UPDATE.

Answer 1

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

• Aaron Bertrand: The first example is wasteful and can often lead to deadlocks - I wouldn't suggest it at all.
• Hexo: @AaronBertrand care to elaborate? Thanks
• Aaron Bertrand: @SlapY Sure, in the first example, you are saying: "Hey, SQL Server, is there a row with this ID?" SQL Server goes off to find the row, perhaps using a scan, and then comes back with the answer. "Why, yes, user, I do have a row with that ID!" Then you say, "Okay, SQL Server, go find that row again, but this time, update it!" Do you see how performing the seek or scan twice is wasteful? Can you imagine what happens if another user asks SQL Server the same question about the existence of a row, before you've moved on to doing something about it?
• Hexo: Thanks, I just don't see why the first is threatend to deadlock while the second isnt? Both consist of multiple statements that can be intercepted if not run with full lock. Am I wrong?
• Aaron Bertrand: @0x25b3 It isn't that one is threatened by deadlocks and the other isn't, it's that the first example is much more prone to them. You should be wrapping in a full and proper transaction in either case, but people don't, so...

Answer 2

Assuming SQL Server 2008 or later, you could use MERGE:

Table

CREATE TABLE dbo.Test
(
 id integer NOT NULL,
 name varchar(30) NULL,
 CONSTRAINT PK_dbo_Test__id
 PRIMARY KEY CLUSTERED (id)
);

Query

MERGE dbo.Test WITH (SERIALIZABLE) AS T
USING (VALUES (3012, 'john')) AS U (id, name)
 ON U.id = T.id
WHEN MATCHED THEN 
 UPDATE SET T.name = U.name
WHEN NOT MATCHED THEN
 INSERT (id, name) 
 VALUES (U.id, U.name);

The SERIALIZABLE hint is required for correct operation under high concurrency.

You can find a comparisons of the common methods by Michael J. Swart here:

Mythbusting: Concurrent Update/Insert Solutions

• vonPryz: Merge has some issues.
• JonnyRaa: the mythbusting link there is excellent. Nice one!