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

  • 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?

  • 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 6 days, 3 hours ago by  ratbak.
  • 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;
  • 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
  • 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;

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

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