UPDATE Table SET Field
- by davlyo
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?
