20

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?

  • 1
    But I wanted to use single query? Why? – Aaron Bertrand Jan 20 '15 at 14:48
  • @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 – SpringLearner Jan 21 '15 at 6:05
  • 1
    Java doesn't support a stored procedure, or a single batch with two statements requiring only one hit to the database? – Aaron Bertrand Jan 21 '15 at 12:27
  • @AaronBertrand could you give an example of how you would handle this with sql server 2008 or later? – eaglei22 May 10 at 18:16
  • 1
    @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. – Aaron Bertrand May 10 at 18:53
34

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

  • 4
    The first example is wasteful and can often lead to deadlocks - I wouldn't suggest it at all. – Aaron Bertrand Jan 20 '15 at 14:48
  • @AaronBertrand care to elaborate? Thanks – Hexo Aug 21 '17 at 12:44
  • 2
    @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? – Aaron Bertrand Aug 21 '17 at 13:25
  • 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? – Hexo Aug 21 '17 at 13:30
  • 2
    @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... – Aaron Bertrand May 10 at 18:55
14

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.