SQL Update语句的理解(Understanding of SQL update statements)

在SQL中,更新操作包括一次删除操作和一次插入操作。
首先是从操作表中获取要更新的记录的集合,并进行修改后暂时保存该集合,可能类似于

Select Col1, Col2, Col3, UpdatedCol = UpdateValueExpr ...
Into Temp
From SourceTable
Where Condition

然后再从原表中删除这些记录,

Delete From SourceTable
Where Condition

再把Inserted表中的记录插入回原表

Insert into SourceTable
  Select * from Temp

也就是说,更新操作是先把要保存修改好的记录,才会对原表进行操作,在此之前原表数据不会发生改变
所以可以有这样的Update语句

//如果Name字段与上一行的相同,则设置为‘’,否则不改变
Update a
Set Name = Case When Exists(Select * from SourceTable Where RowID = a.RowID And Name = a.Name) Then ‘’ Else Name End
From SourceTable a
————————

In SQL, the update operation includes a delete operation and an insert operation.
First, get the set of records to be updated from the operation table, and save the set temporarily after modification, which may be similar to

Select Col1, Col2, Col3, UpdatedCol = UpdateValueExpr ...
Into Temp
From SourceTable
Where Condition

Then delete these records from the original table,

Delete From SourceTable
Where Condition

Then insert the records in the inserted table back to the original table

Insert into SourceTable
  Select * from Temp

In other words, the update operation is to save the modified records before operating on the original table. Before that, the data of the original table will not be changed
So you can have such an update statement

//如果Name字段与上一行的相同,则设置为‘’,否则不改变
Update a
Set Name = Case When Exists(Select * from SourceTable Where RowID = a.RowID And Name = a.Name) Then ‘’ Else Name End
From SourceTable a