CODEWITHSUNDEEP
c#sqldatabasesql-server-2008
OmniOwl
OmniOwl

Reputation: 5709

Update Existing Records with SQL

This question seems kind of silly but I ask because my SQL have become quite rusty.

I am trying to do a simple Update in a Microsoft SQL Database. I have an object representation of a Database table entry that I created myself.

It got all the fields that the Database table has and now all I need to do is update the original record in the database with the new data I got from a client application. I get every field from the client, even fields that weren't changed. I need to somehow make an SQL statement that:

  1. Finds an existing record in the Database.
  2. Updates the record only where the fields differ in data.

I am not even sure if it's possible to check for that in an SQL statement or if I simply have to just overwrite data regardless of it being the same as the original or not.

Upvotes: 0

Views: 956

Answers (5)

hidden
hidden

Reputation: 3236

So here is a methodology:

1.) Do a equi join
Table1
FirstName LastName PrimaryKey
Jay       Donner    1
Steven    Woods     2
Bruce     Beard     3
Table2 
Firstname LastName PrimaryKey
Jay       Donner    1
Steven    Wood      2
Bruce     Bears     3

So Inner join 

Select A.FirstName,A.LastName,A.PrimaryKey
From Table1 A INNER JOIN Table2 B
On A.FirstName = B.FirstName 
AND A.LastName = B.LastName

Now the result set will be the fields that match Jay Donner 1

Since you want to find the fields that do not match you can use a subquery using the 'not in' command, as shown below:

Select  A.FirstName,A.LastName,A.PrimaryKey
From Table1 A INNER JOIN Table2 B 
ON A.PrimaryKey =B.PrimaryKey
Where A.PrimaryKey NOT IN (
    Select A.PrimaryKey
    From Table1 A INNER JOIN Table2 B
    On A.FirstName = B.FirstName 
    AND A.LastName = B.LastName
)

So the above query states: Find things that the primary key matches and for which the properties do not match values in the firstname and lastname fields. 

Steven    Woods     2
Bruce     Bear      3

Now you know the primary keys of the things you need to update. Next, iterate through your table with a update statement. Cursor is a piece of cake but I guess someone with tell you here to use recursion with CTE. Good luck with that...this is why I hate SQL. Here is an example of recursion using a cursor... So personally I would use c# with Entity Framework do this task and wrap it inside of as SSIS package with Executable command that I can put as a job in my SQL Server. Anyways!.. below is the iteration.

Here the example code with no comments.

Declare @FName as nvarchar(50)

Declare @LName as nvarchar(100)

Declare @PrimaryKeyas int

Declare PersonCursor CURSOR FAST_FORWARD FOR


Select  A.FirstName,A.LastName,A.PrimaryKey
From Table1 A INNER JOIN Table2 B 
ON A.PrimaryKey =B.PrimaryKey
Where A.PrimaryKey NOT IN (
    Select A.PrimaryKey
    From Table1 A INNER JOIN Table2 B
    On A.FirstName = B.FirstName 
    AND A.LastName = B.LastName
)


OPEN PersonCursor

FETCH NEXT FROM PersonCursor

INTO @FName, @LName, @Email


WHILE @@FETCH_STATUS = 0

BEGIN

-- do row specific stuff here



  UPDATE table2
   SET [EmployeeIdentification]=  @Fname, LastName=@LastName
   WHERE PrimaryKey=@PrimaryKey


FETCH NEXT FROM PersonCursor

INTO @FName, @LName, @Email

END


CLOSE PersonCursor

DEALLOCATE PersonCursor

Upvotes: 0

Md. Parvez Alam
Md. Parvez Alam

Reputation: 4596

Well dont have enough time to write query, but for a quick help the merge command can do the things you want.

follow the following links

http://www.cs.utexas.edu/~cannata/dbms/SQL%20Merge.html

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

http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/

Upvotes: 0

Rikalous
Rikalous

Reputation: 4564

You can do it but it gets very long winded very quickly and if there a nullable fields involved it gets horrendous. The standard approach, for a long time, has been just to overwrite all the fields. If you want to avoid stomping over other peoples edits, I suggest you use a Timestamp field and just check that single column value hasn't changed when you do the update.

Upvotes: 0

logixologist
logixologist

Reputation: 3834

Finds an existing record in the Database

I would put it in a stored procedure.

or if I simply have to just overwrite data regardless of it being the same as the original or not.

I personally have used your approach where it updates all every time. Just make sure you are not updating only those columns that are not part of the primary key. Example: UPDATE table set firstname = @firstname, @lastname = lastname where userid = @userid. just make sure you dont update UserID.

It wasnt clear based on your question, did you need to see actual code?

Upvotes: 0

Dizzy B
Dizzy B

Reputation: 251

So I guess you're using ADO.NET...

When you load your object you'll be populating the primary key equivalent field on the object. Assuming it's an int identity or what-not, it'll be 0 if you're creating a new object. Otherwise, it'll be the value of what you got when you loaded it in from the database.

Of course, you'll have to ensure that value is there with whatever binding you're doing.

So when you're persisting the object to the database, check the primary key value on the object and call either an insert or an update method accordingly.

As Dai said however, your mileage will be intensely better if you select an ORM. Entity Framework has a bit of a learning curve. While I'm not a fan of PetaPoco it's pretty good for (very) small-scale projects.

Upvotes: 1

Related Questions