This is my Very first Post! Bear with me. I have an Update Statement that I am trying to understand how SQL Server handles it.

UPDATE a
   SET a.vField3 = b.vField3
  FROM tableName a
INNER JOIN tableName b ON a.vField1 = b.vField1
                      AND b.nField2 = a.nField2 – 1

This is my query in its simplest form. vField1 is a Varchar nField2 is an int (autonumber) vField3 is a Varchar

I have left the WHERE clause out so understand there is logic that otherwise makes this a nessessity.

Say vField1 is a Customer Number and that Customer has 3 records The value in nField2 is 1, 2, and 3 consecutively. vField3 is a Status

When the Update comes to a.nField2 = 1 there is no a.nField2 -1 so it continues When the Update comes to a.nField2 = 2, b.nField2 = 1 When the Update comes to a.nField2 = 3, b.nField2 = 2

So when the Update is on a.nField2 = 2, alias b reflects what is on the line prior (b.nField2 = 1) And it SETs the Varchar Value of a.vField3 = b.vField3

When the Update is on a.nField2 = 3, alias b reflects what is on the line prior (b.nField2 = 2) And it (should) SET the Varchar Value of a.vField3 = b.vField3

When the process is complete –the Second of three records looks as expected –hence the value in vField3 of the second record reflects the value in vField3 from the First record

However, vField3 of the Third record does not reflect the value in vField3 from the Second record.

I think this demonstrates that SQL Server may be producing a transaction of some sort and then an update.

Question: How can I get the DB to Update after each transaction so I can reference the values generated by each transaction?