Sql Update, Same Query, Different Results Each Time

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 differen

Solution 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.

Solution 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.