How can I tell if an UPDATE command updated any rows?

Question

Post reply

How can I tell if an UPDATE command updated any rows?

tim8w

Old Hand

Points: 377
More actions
October 29, 2024 at 2:48 pm
Go to Answer
#4474997

I am calling an Update routine from C# and need to determine if the Update routine found a matching row to update. If it does not, I call the Insert Routine. I was hoping that if no row was updated, I would get an error which would tell me that I need to do the insert.
Is there a way to determine if a row was updated?

DesNorton

SSC-Insane

Points: 24416

October 29, 2024 at 5:00 pm

Answer

#4475063

Why not pass your values to a stored procedure.

Then in the stored procedure you can do a proper upsert pattern

-- Inherent in the WHERE clause is the guarantee that we
-- can't update anything if the matching row isn't there.
UPDATE SchemaName.TableName
SET FieldName = PassedInValue
WHERE KeyField = PassedInKey;

IF ( @@ROWCOUNT = 0 ) -- Nothing was updated, so attempt to insert the value as a new record
BEGIN
  -- Here we use the WHERE to ensure that we don't
  -- insert a row if there's already a matching row.
  INSERT INTO SchemaName.TableName ( SomeKey, SomeField )
  SELECT SomeKeyValue, SomeValue
                    -- The hints are needed to ensure that two sessions
                    -- can't check the existance of the row at the same time
  WHERE NOT EXISTS (SELECT 1 FROM SchemaName.TableName WITH (XLOCK, HOLDLOCK)
                    WHERE SomeKey = SomeKeyValue
                    );
END;

How to post data/code on a forum to get the best help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1382 More actions · Answer 1

What do you mean by "Update routine"? A stored procedure? A script? Or ORM or other C# code

If SQL code add something like

SELECT @@ROWCOUNT AS UpdatedCount

directly after the update.

Or you could modify the return code to return a non-zero value if @@ROWCOUNT is zero.

This reply was modified 1 month, 4 weeks ago by ratbak.

tim8w Old Hand Points: 377 More actions · Answer 2

I am using a Stored Procedure. I tried modifying it as you suggested and it updated the row and inserted the new ones correctly. Thanks for the help.

Here's the updated Stored Procedure:

CREATE PROCEDURE [dbo].[insupd_tblMRBHistory]
   (@BusinessUnit char(30)
           ,@PartNum char(25)
   ,@Description varchar(50)
   ,@Category char(4)
   ,@LotNum char(25)
   ,@LocationID int
   ,@StockMRB int
   ,@ExtendedCost decimal(7,2)
   ,@TransDate datetime
   ,@MRBDays int
   ,@Closed bit)
AS
BEGIN
UPDATE [dbo].[tblMRBHistory]
SET        [BusinessUnit] = @BusinessUnit,
           [PartNum] = @PartNum,
   [Description] = @Description,
           [Category] = @Category,
   [LotNum] = @LotNum,
   [LocationID] = @LocationID,
   [StockMRB] = @StockMRB,
   [ExtendedCost] = @ExtendedCost,
   [MRBDays] = @MRBDays,
   [Closed] = @Closed
WHERE [PartNum] = @PartNum AND [LotNum] = @LotNum

IF (@@RWOCOUNT = 0)
INSERT INTO [dbo].[tblMRBHistory]
           ([BusinessUnit],
           [PartNum],
   [Description],
           [Category],
   [LotNum],
   [LocationID],
   [StockMRB],
   [ExtendedCost],
   [TransDate],
   [MRBDays],
   [Closed])
     VALUES
           (@BusinessUnit,
           @PartNum,
   @Description,
           @Category,
   @LotNum,
   @LocationID,
   @StockMRB,
   @ExtendedCost,
   @TransDate,
   @MRBDays,
   @Closed)
END

DesNorton SSC-Insane Points: 24416 More actions · Answer 3

If you have multiple threads, the inserting of VALUES could result in duplicates.

I would modify this to use the WHERE NOT EXISTS

IF (@@RWOCOUNT = 0)
BEGIN
  INSERT INTO dbo.tblMRBHistory ( BusinessUnit, PartNum, Description, Category, LotNum, LocationID, StockMRB, ExtendedCost, TransDate, MRBDays, Closed )
  SELECT @BusinessUnit, @PartNum, @Description, @Category, @LotNum, @LocationID, @StockMRB, @ExtendedCost, @TransDate, @MRBDays, @Closed
  WHERE NOT EXISTS ( SELECT 1 FROM [dbo].[tblMRBHistory] WITH (XLOCK, HOLDLOCK)
                     WHERE [PartNum] = @PartNum AND [LotNum] = @LotNum);
END;

How to post data/code on a forum to get the best help.

kaj SSCommitted Points: 1607 More actions · Answer 4

Michael J. Swart has a nice write-up about different kinds of upserts (and what's good and what's not):

https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/