How to insert or update using single query?

  • 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?

    `But I wanted to use single query?` Why?

    @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

    Java doesn't support a stored procedure, or a single batch with two statements requiring only one hit to the database?

    @AaronBertrand could you give an example of how you would handle this with sql server 2008 or later?

    @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.

    FWIW, the MySQL/MariaDB equivalent is `ON DUPLICATE KEY UPDATE`.

    @AaronBertrand That is a great blog post, I'm glad I've read it. What I need though is a generic (or code generated way to write the boilerplate) routine to keep *several* local snapshots up to date based on a last_updated timestamp - it seems insane there's not a use at your own risk "upsert" command that supports "select * from" and "just works" based on a matching schema and a specified primary key(s).

  • 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

    The first example is wasteful and can often lead to deadlocks - I wouldn't suggest it at all.

    @AaronBertrand care to elaborate? Thanks

    @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?

    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?

    i like the send statement but how would it work to do it for two full tables not just a single record?

    @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...

  • 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

    the mythbusting link there is excellent. Nice one!

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM