SQL Update, same query, different results each tim

问题:

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?

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??

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

回答1:

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.

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.

回答2:

The only way that this will not result in the same output on each run would be that either you are doing something else outside of this, somebody else is doing something else outside of this, or there is a possibility this could get wonky if you have more than one open transaction. If it is the latter, and/or to test it, just run ROLLBACK TRAN until you get an error stating that there are no open transactions. If you get the error the first time, then you did not have any open.