SQL 如果不同/更改则更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6677517/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update if different/changed
提问by nami
Is it possible to perform an update statement in sql, but only update if the updates are different?
是否可以在sql中执行更新语句，但只有在更新不同时才更新？
for example
例如
if in the database, col1 = "hello"
如果在数据库中， col1 = "hello"
update table1 set col1 = 'hello'
should notperform any kind of update
不应执行任何类型的更新
however, if
然而，如果
update table1 set col1 = "bye"
this shouldperform an update.
这应该执行更新。
采纳答案by Daniel Hilgarth
This is possible with a before-update trigger.
In this trigger you can compare the old with the new values and cancel the update if they don't differ. But this will then lead to an error on the caller's site.
I don't know, why you want to do this, but here are several possibilities:
这可以通过更新前触发器实现。在此触发器中，您可以将旧值与新值进行比较，如果它们没有不同，则取消更新。但这会导致调用者站点出现错误。
我不知道你为什么要这样做，但这里有几种可能性：
- Performance: There is no performance gain here, because the update would not only need to find the correct row but additionally compare the data.
- Trigger: If you want the trigger only to be fired if there was a real change, you need to implement your trigger like so, that it compares all old values to the new values before doing anything.
- 性能：这里没有性能提升，因为更新不仅需要找到正确的行，还需要比较数据。
- 触发器：如果您只想在发生真正的更改时触发触发器，则需要像这样实现触发器，以便在执行任何操作之前将所有旧值与新值进行比较。
回答by sll
The idea is do not perform any update if a new value is the same as in DB right now
如果新值与 DB 中的值相同，则不执行任何更新
WHERE col1 != @newValue
(obviously there is also should be some Idfield to identify a row)
（显然也应该有一些Id字段来标识一行）
WHERE Id = @Id AND col1 != @newValue
PS: Originally you want to do update only if value is 'bye' so just add AND col1 = 'bye', but I feel that this is redundant, I just suppose
PS：本来你只想在 value 为 'bye' 时才进行更新，所以只需 add AND col1 = 'bye'，但我觉得这是多余的，我只是想
回答by Dude0001
During query compilation and execution, SQL Server does not take the time to figure out whether an UPDATE statement will actually change any values or not. It just performs the writes as expected, even if unnecessary.
在查询编译和执行期间，SQL Server 不会花时间确定 UPDATE 语句是否会实际更改任何值。它只是按预期执行写入，即使是不必要的。
In the scenario like
在这样的场景中
update table1 set col1 = 'hello'
you might think SQL won't do anything, but it will – it will perform all of the writes necessary as if you'd actually changed the value. This occurs for both the physical table (or clustered index) as well as any non-clustered indexes defined on that column. This causes writes to the physical tables/indexes, recalculating of indexes and transaction log writes. When working with large data sets, there is huge performance benefits to only updating rows that will receive a change.
您可能认为 SQL 不会执行任何操作，但它会执行所有必要的写入操作，就好像您确实更改了值一样。这对于物理表（或聚集索引）以及在该列上定义的任何非聚集索引都会发生。这会导致写入物理表/索引、重新计算索引和事务日志写入。在处理大型数据集时，仅更新将接收更改的行具有巨大的性能优势。
If we want to avoid the overhead of these writes when not necessary we have to devise a way to check for the need to be updated. One way to check for the need to update would be to add something like “where col <> 'hello'.
如果我们想在不必要的时候避免这些写入的开销，我们必须设计一种方法来检查是否需要更新。检查是否需要更新的一种方法是添加类似“where col <> 'hello'.
update table1 set col1 = 'hello' where col1 <> 'hello'
But this would not perform well in some cases, for example if you were updating multiple columns in a table with many rows and only a small subset of those rows would actually have their values changed. This is because of the need to then filter on all of those columns, and non-equality predicates are generally not able to use index seeks, and the overhead of table & index writes and transaction log entries as mentioned above.
但是在某些情况下这不会很好地执行，例如，如果您要更新具有许多行的表中的多个列，并且只有这些行的一小部分实际上会更改它们的值。这是因为需要对所有这些列进行过滤，并且非相等谓词通常无法使用索引查找，以及如上所述的表和索引写入以及事务日志条目的开销。
But there is a much better alternative using a combination of an EXISTS clause with an EXCEPT clause. The idea is to compare the values in the destination row to the values in the matching source row to determine if an update is actually needed. Look at the modified query below and examine the additional query filter starting with EXISTS. Note how inside the EXISTS clause the SELECT statements have no FROM clause. That part is particularly important because this only adds on an additional constant scan and a filter operation in the query plan (the cost of both is trivial). So what you end up with is a very lightweight method for determining if an UPDATE is even needed in the first place, avoiding unnecessary write overhead.
但是，使用 EXISTS 子句和 EXCEPT 子句的组合有一个更好的选择。这个想法是将目标行中的值与匹配源行中的值进行比较，以确定是否确实需要更新。查看下面修改后的查询并检查以 EXISTS 开头的附加查询过滤器。请注意 EXISTS 子句内部的 SELECT 语句如何没有 FROM 子句。这部分特别重要，因为这只会在查询计划中增加额外的常量扫描和过滤操作（两者的成本微不足道）。所以你最终得到的是一个非常轻量级的方法，用于确定是否需要更新，避免不必要的写入开销。
update table1 set col1 = 'hello'
/* AVOID NET ZERO CHANGES */
where exists
(
/* DESTINATION */
select table1.col1
except
/* SOURCE */
select col1 = 'hello'
)
This looks overly complicated vs checking for updates in a simple WHERE clause for the simple scenerio in the original question when you are updating one value for all rows in a table with a literal value. However, this technique works very well if you are updating multiple columns in a table, and the source of your update is another query and you want to minimize writes and transaction logs entries. It also performs better than testing every field with <>.
当您使用文字值更新表中所有行的一个值时，这看起来过于复杂，而不是检查原始问题中简单场景的简单 WHERE 子句中的更新。但是，如果您要更新表中的多个列，并且更新源是另一个查询，并且您希望最小化写入和事务日志条目，则此技术非常有效。它也比用 <> 测试每个字段的性能更好。
A more complete example might be
一个更完整的例子可能是
update table1
set col1 = 'hello',
col2 = 'hello',
col3 = 'hello'
/* Only update rows from CustomerId 100, 101, 102 & 103 */
where table1.CustomerId IN (100, 101, 102, 103)
/* AVOID NET ZERO CHANGES */
and exists
(
/* DESTINATION */
select table1.col1
table1.col2
table1.col3
except
/* SOURCE */
select z.col1,
z.col2,
z.col3
from #anytemptableorsubquery z
where z.CustomerId = table1.CustomerId
)
回答by ypercube??
If you want to change the field to 'hello'only if it is 'bye', use this:
如果要将字段更改为'hello'仅当它是时'bye'，请使用以下命令：
UPDATE table1
SET col1 = 'hello'
WHERE col1 = 'bye'
If you want to update only if it is different that 'hello', use:
如果您只想在与 不同的情况下更新'hello'，请使用：
UPDATE table1
SET col1 = 'hello'
WHERE col1 <> 'hello'
Is there a reason for this strange approach? As Daniel commented, there is no special gain - except perhaps if you have thousands of rows with col1='hello'. Is that the case?
这种奇怪的方法有什么原因吗？正如丹尼尔评论的那样，没有什么特别的好处——除非你有数千行col1='hello'. 是这样吗？
回答by Tvitmsvleli
CREATE OR REPLACE PROCEDURE stackoverflow([your_value] IN TYPE) AS
BEGIN
UPDATE [your_table] t
SET t.[your_collumn] = [your_value]
WHERE t.[your_collumn] != [your_value];
COMMIT;
EXCEPTION
[YOUR_EXCEPTION];
END stackoverflow;
回答by Claudio Bogado Pompa
You need an unique key idin your table, (let's suppose it's value is 1) to do something like:
您id的表中需要一个唯一键（假设它的值为 1）来执行以下操作：
UPDATE table1 SET col1="hello" WHERE id=1 AND col1!="hello"
回答by maxTrialfire
Old question but none of the answers correctly address nullvalues.
老问题，但没有一个答案正确地解决了null值。
Using <> or != will get you into trouble when comparing values for differences if there are is potential null in the new or old value to safely update only when changed use the is distinct fromoperator in Postgres. Read more about it here
使用 <> 或 != 比较差异值时会遇到麻烦，如果新值或旧值中存在潜在的空值，只有在更改时使用is distinct fromPostgres 中的运算符才能安全更新。在此处阅读更多相关信息
回答by Rajat Thapar
I think this should do the trick for ya...
我认为这应该对你有用......
create trigger [trigger_name] on [table_name]
for insert
AS declare @new_val datatype,@id int;
select @new_val = i.column_name from inserted i;
select @id = i.Id from inserted i;
update table_name set column_name = @new_val
where table_name.Id = @id and column_name != @new_val;
