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=30122if it has some records then I can use
update test set name='john' where id=3012or 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?
@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');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
SERIALIZABLEhint is required for correct operation under high concurrency.You can find a comparisons of the common methods by Michael J. Swart here:
Merge has some issues.
the mythbusting link there is excellent. Nice one!
License under CC-BY-SA with attribution
Content dated before 6/26/2020 9:53 AM
Aaron Bertrand 6 years ago
`But I wanted to use single query?` Why?