Insert and alter in one statement

Question

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))

Depending on database engine there are different implementations, for instance MySQL uses `INSERT... ON DUPLICATE KEY` and MSSQL uses `MERGE`. If you post which RDBMS I can point you in the direction of the correct syntax for the specific db — Steph Locke, Jul 08 '13 at 10:53
@StephLocke Sorry - I forgot the variety of DBs. It's SQL Server in the cloud, i.e. Azure. I'm going to give *MERGE* a whack and you're welcome to post the comment as a reply so I can check it as an answer if the suggestion works. — Konrad Viltersten, Jul 08 '13 at 11:13

score 3 · Answer 1 · answered Jul 08 '13 at 11:04

3

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

answered Jul 08 '13 at 11:04

yajay

1,050
10
24

Sorry, my bad - didn't tell it's SQL Azure. I keep forgetting that most people can't read minds... :) – Konrad Viltersten Jul 08 '13 at 11:16

score 1 · Answer 2 · answered Jul 08 '13 at 11:03

1

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

answered Jul 08 '13 at 11:03

Amar Agrawal

141
13

Sorry, my bad - didn't tell it's SQL Azure. I keep forgetting that most people can't read minds... :) – Konrad Viltersten Jul 08 '13 at 11:15

score 1 · Accepted Answer · edited May 23 '17 at 12:06

1

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

edited May 23 '17 at 12:06

Community

1
1

answered Jul 08 '13 at 11:24

Steph Locke

5,951
4
39
77

I've checked out *MERGE* but it's really confusing. The canonical parts, I get, but the last line with the dollar signs and such is just too much. Am I looking at it the wrong way? I suspect that I'm getting into syntax that has all the bells and whistles while really needing only a basic case scenario. Please advise. – Konrad Viltersten Jul 08 '13 at 15:44
Dim problem - could you provide a schema in your question, that way I help can generate some SQL – Steph Locke Jul 08 '13 at 15:47
That would be very kind of you. Please see the edit in my question. The column that is supposed to recognize the "sameness" of the record is the unique int *Beep* (the *Id* is there only for the same of consequence). Normally I'm reluctant to asking others to do all the work because I prefer to "kill my own food" but it sounds like if it's pretty straight-forward, so I'll take it. :) – Konrad Viltersten Jul 08 '13 at 18:09
Yes, it helped a lot. Two more things, though. (1) If I wish to update many columns in a table - is there a more compact syntax than lining them up as in my edit to your answer? (2) Is it a standard naming to describe the source's parts as the column names? I took the liberty to edit that as well. Correct me if you've found it inappropriate, please. – Konrad Viltersten Jul 09 '13 at 12:43
1) Don't think so - this is standard update syntax - but could be wrong 2) You can name them anything you like - I just prefer to name things as what they are to make things more clear later on down the line for debugging/modification purposes – Steph Locke Jul 09 '13 at 12:57
Perfect answer! I was hoping for something like *...then insert into #Stuff TheNewThing* but I guess SQL syntax is the way it is. Would you like me to bounty you? :) – Konrad Viltersten Jul 09 '13 at 13:01
Thanks but no need to - I'm just helping out while R queries run! – Steph Locke Jul 09 '13 at 13:08

Insert and alter in one statement

3 Answers3