47

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?

7
  • 2
    But I wanted to use single query? Why? Jan 20, 2015 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 Jan 21, 2015 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? Jan 21, 2015 at 12:27
  • 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. May 10, 2019 at 18:53
  • 1
    FWIW, the MySQL/MariaDB equivalent is ON DUPLICATE KEY UPDATE. Aug 22, 2019 at 9:41

2 Answers 2

62

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

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

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

2
  • 8
    Merge has some issues.
    – vonPryz
    Jan 20, 2015 at 14:00
  • the mythbusting link there is excellent. Nice one!
    – JonnyRaa
    Nov 20, 2019 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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