更新，但只有不同之处(已更改的内容)

Question

我试过这样做:

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;

但是不工作。

Answer 1

如果Id是主键，那么您可能需要:

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);

但是通常将列更新为相同的值不会有任何危害，除非你有一些不想运行的触发器。即使使用上面的代码，如果只有一个的列值发生了变化，您将使用"update">

Answer 2

这是防止不必要更新的另一种方法。它使用基于set的操作符INTERSECT，并且在比较时不会被NULL值绊倒:

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

解决方案是使用UpdatedOnDATETIMEOFFSET(3)列来跟踪更新的DateTime。

/p>

-- 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', '[e-mail]', NULL),
(2, 'Paul', NULL, '[e-mail]', 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) = '[e-mail]';
;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;