SQL更新，同一个查询，每次结果不同 [英] SQL Update, same query, different results each time

问题描述

我有一个正在测试的 SQL 查询，并按如下方式运行，但我注意到它似乎每次都返回不同的数据，然后我意识到当我检查它是否返回不同数量的行时工作了！！我已经多次运行它，最终的 select 语句将返回 25-32 行之间的某处，但如何改变?

I have an SQL query that I am testing, and running as below, but I noticed that it seemed to be returning different data each time, then i realised it was even returning a different amount of rows when i check if it has worked!! I have run it multiple times and the final select statement will return somewhere between 25-32 rows, but how can this change?

我正在使用 begin tran 和 rollback tran 来处理相同的数据，并且不相信这是问题所在.谁能发现我做错了什么?

I am using begin tran and rollback tran to work on the same data, and don't believe this is the issue. Can anyone spot what i have done wrong??

它在一个表 (#AddressToDeleteMasterOfLesserId) 上工作，它是成对的 Id 并在客户地址上设置一个标志 (IsPrimaryAddress)，如果它存在于表中并且它的对设置了标志.#AddressToDeleteMasterOfLesserId 已定义且不会更改.

It works on a table(#AddressToDeleteMasterOfLesserId) which is pairs of Id's and sets a flag (IsPrimaryAddress) on the Customer address if it exists in the table and it's pair has the flag set. #AddressToDeleteMasterOfLesserId has already been defined and does not change.

    begin tran t1

    select CustomerAddress.IsPrimaryAddress, p1.[Id that is master],p1.[Id to delete], c2.IsPrimaryAddress
    FROM CustomerAddress
    join #AddressToDeleteMasterOfLesserId p1 on CustomerAddress.Id=p1.[Id that is master]
    join CustomerAddress c2 on p1.[Id to delete]=c2.Id
    order by [Id that is master]

    --Update primary address
    UPDATE CustomerAddress
    SET IsPrimaryAddress = CASE WHEN c2.IsPrimaryAddress=1 THEN 1 ELSE 0 END
    FROM CustomerAddress
    join #AddressToDeleteMasterOfLesserId p1 on CustomerAddress.Id=p1.[Id that is master]
    join CustomerAddress c2 on p1.[Id to delete]=c2.Id

    select CustomerAddress.IsPrimaryAddress, p1.[Id that is master],p1.[Id to delete], c2.IsPrimaryAddress
    FROM CustomerAddress
    join #AddressToDeleteMasterOfLesserId p1 on CustomerAddress.Id=p1.[Id that is master]
    join CustomerAddress c2 on p1.[Id to delete]=c2.Id
    where CustomerAddress.IsPrimaryAddress=0
     and c2.IsPrimaryAddress=1
    order by [Id that is master]

    rollback tran t1

推荐答案

你的 #AddressToDeleteMasterOfLesserId 表必须包含一些配对，其中相同的 Id that is master 配对多个Id to delete和那些Ids to delete在CustomerAddress表中具有不同的IsPrimaryAddress匹配值.

Your #AddressToDeleteMasterOfLesserId table must be holding some pairs where the same Id that is master is paired with more than one Id to delete and those Ids to delete have different matching values of IsPrimaryAddress in the CustomerAddress table.

在更新阶段，这样的Id that is master 行的IsPrimaryAddress 随机更新为 1 或 0，具体取决于要删除的匹配 Idcode> 行被选为新值的来源.

At the update stage, such Id that is master row's IsPrimaryAddress is updated randomly with either 1 or 0, depending upon which matching Id to delete row gets chosen to be the source of the new value.

这篇关于SQL更新，同一个查询，每次结果不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！