Update but only what is different (what've changed)-CodePudding

I've tried something like this:

UPDATE Person 
SET Name = @Name, Phone = @Phone, Email = @Email, Status = @Status 
WHERE Id = @Id AND Name != @Name AND Phone != @Phone AND Email != @Email AND Status != @Status;

But isn't working.

CodePudding user response：

Here is another way to prevent unnecessary updates. It is using set based operator INTERSECT, and it won't trip on NULL values in comparison:

AND (Name != @Name OR Phone != @Phone OR Email != @Email)

The solution is using UpdatedOn DATETIMEOFFSET(3) column to track the updated DateTime.

SQL

-- DDL and sample data population, start
DECLARE @person TABLE (
    ID INT PRIMARY KEY, 
    Name VARCHAR(20), 
    Phone VARCHAR(15), 
    Email VARCHAR(128), 
    UpdatedOn DATETIMEOFFSET(3)
);
INSERT @person (ID, Name, Phone, Email, UpdatedOn) VALUES
(1, 'Peter', '1-305-803-1234', '[email protected]', NULL),
(2, 'Paul', NULL, '[email protected]', NULL);
-- DDL and sample data population, end

-- before
SELECT * FROM @person;

DECLARE @ID INT = 1
    , @Name VARCHAR(20) = 'Peter' -- try 'PeterZ'
    , @Phone VARCHAR(15) = '1-305-803-1234'
    , @Email VARCHAR(128) = '[email protected]';

;WITH rs AS
(
    SELECT @ID AS ID
        , @Name AS NAme
        , @Phone AS Phone
        , @Email AS Email
)
UPDATE T 
SET Name = S.Name, Phone = S.Phone, Email = S.Email
   , T.UpdatedOn = SYSDATETIMEOFFSET()
FROM @person AS T       -- target
    INNER JOIN rs AS S  -- source
      ON T.ID = S.ID
WHERE NOT EXISTS (SELECT S.* 
            INTERSECT SELECT T.ID, T.Name, T.Phone, T.Email);

-- test
SELECT * FROM @person;

CodePudding user response：

If Id is your primary key then you probably want:

UPDATE Person 
SET Name = @Name, Phone = @Phone, Email = @Email, Status = @Status 
WHERE Id = @Id AND 
   (Name != @Name OR Phone != @Phone OR Email != @Email OR Status != @Status);

But there generally isn't any harm in updating columns to the same value unless you have some sort of trigger that you don't want to run. Even with the code above, if only one of the column values changes, you're going to "update" the other three to their same value.