CODEWITHSUNDEEP
sqlazure-sql-database
Konrad Viltersten
Konrad Viltersten

Reputation: 39308

Insert and alter in one statement

I'd like to store a set of data into a database but if it's a pre-existing record, I'd like to alter it. Otherwise, create a new one. Is there a combine statement for that? (Haven't got any when googling.)

Right now, the best I have is to check if already exists and then perform one of the operations. Seems cumbersome to me.

create table Stuff (
  Id int identity(1001, 1) primary key clustered, 
  Beep int unique,
  Boop nvarchar(50))

Upvotes: 2

Views: 2156

Answers (3)

Steph Locke
Steph Locke

Reputation: 6166

MySQL uses INSERT... ON DUPLICATE KEY and MSSQL uses MERGE

MERGE is supported by Azure, and I can highly recommend this blog article on it, as a good intro to the statement

Here is a merge statement based on the schema provided...

create table #Stuff (
  Id int identity(1001, 1) primary key clustered, 
  Beep int unique,
  Boop nvarchar(50),
  Baap nvarchar(50)
);

INSERT  INTO #Stuff VALUES (1,'boop', 'poop');
INSERT  INTO #Stuff VALUES (2,'beep', 'peep');

SELECT * FROM #STUFF;

MERGE #Stuff 
   USING (VALUES(1,'BeepBeep','PeepPeep')) AS TheNewThing(A,B,C)
     ON #Stuff.Beep = TheNewThing.A
WHEN MATCHED THEN UPDATE SET #Stuff.Boop = TheNewThing.B, #Stuff.Baap = 'fixed'
WHEN NOT MATCHED THEN INSERT (Beep,Boop,Baap) VALUES (
  TheNewThing.A, TheNewThing.B, TheNewThing.C);

SELECT * FROM #STUFF

I also found a really good SO Q which might make good further reading

Upvotes: 1

yajay
yajay

Reputation: 1100

IN MYSQL :

You may use INSERT ... ON DUPLICATE KEY UPDATE .

eg:

INSERT INTO table (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;

For more information: http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

Upvotes: 3

Amar Agrawal
Amar Agrawal

Reputation: 141

yes you can easily do it using pl/sql here is sample code which will help you http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/01_oview.htm#7106

Upvotes: 1

Related Questions