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?

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

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