1

I want to update "Grade" Column in "StudentTable" with a WHERE clause of "StudentID"". And if there is no "StudentID" found in "StudentTable" then I want to Insert the data instead.

How can I do this?

hoodaticus
  • 3,772
  • 1
  • 18
  • 28
user793468
  • 4,898
  • 23
  • 81
  • 126
  • You wanna do this in plane SQL or do you have some kind of programming language ? – Dukeatcoding Jul 20 '11 at 20:04
  • Please tag/specify the version of SQL Server you are using - this information is *always* useful to include in your question. There are "safer" methods to achieve this but they are not necessarily valid if you're on an older version of SQL Server. – Aaron Bertrand Jul 20 '11 at 21:53
  • possible duplicate of [Atomic UPSERT in SQL Server 2005](http://stackoverflow.com/questions/2522379/atomic-upsert-in-sql-server-2005) – ypercubeᵀᴹ Jul 20 '11 at 21:53

2 Answers2

2

You first check if the record exists, if it does perform an update, if it does not exist, it means you will need to insert it.

Here you go:

IF EXISTS(SELECT * FROM StudentTable WHERE StudentID = @MyID)
  BEGIN 
    --exists perform update
    UPDATE StudentTable SET Grade = 'A+' WHERE StudentID=@MyID
    --other code...
  END
ELSE
  BEGIN
    --record does not exist INSERT it
     INSERT INTO StudentTable(MyID, Grade) VALUES (@MyID, 'A+')
    --other code...
  END
JonH
  • 32,732
  • 12
  • 87
  • 145
  • 1
    In T-SQL, the comment specifier is --, not apostrophe. And the BEGIN/END stuff isn't necessary here. But +1 cuz it's a good answer. – hoodaticus Jul 20 '11 at 20:08
  • 2
    @hoodaticus - sorry working on a vb.net app at the moment and comments in vb.net are '. Sorry, and the begin / end are not needed if no other statements exist, he / she may have other code so they can determine whether to leave the begin / end statements. – JonH Jul 20 '11 at 20:10
  • I thought it was VB leakage :) – hoodaticus Jul 20 '11 at 20:14
  • 2
    This won't scale because 2 processes overlapping could see "NOT EXIST" and try to INSERT. See http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there/3408196#3408196 and http://stackoverflow.com/questions/3593870/select-insert-version-of-an-upsert-is-there-a-design-pattern-for-high-concurre/3594328#3594328 for how to do it. For why, see http://stackoverflow.com/questions/6709935/how-sql-server-handles-concurrent-requests/6709958#6709958 – gbn Jul 20 '11 at 20:20
  • Thanks JonH, Will the above solution work if I have two rows for a StudendID: "JD1212" and want to update the two and Insert One for the Same Student? – user793468 Jul 20 '11 at 20:23
1

You can use merge: http://www.mssqltips.com/tip.asp?tip=1704

MSDN documentation: http://msdn.microsoft.com/en-us/library/bb510625.aspx

mservidio
  • 12,817
  • 9
  • 58
  • 84