UPDATE Table SET Field

Question

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?

Thank you. davlyo

Answer 1

The whole update query is one operation - and one transaction if that's the only thing in the transaction. So, the query does not see it's own results. The query operates without any implied order - almost as if it all happens at once.

Also bear in mind that this is a self join, so what was originally the second/third record will not be after the query is run. One record will be "lost" - the original third record, and the record at value 1 duplicated.

E.g. you start with, Customer, aField2, aField3

mdma    1     A
mdma    2     B
mdma    3     C

After running your update, the values will be

mdma    1    A
mdma    2    A
mdma    3    B

Is that what you are seeing/expecting?

Answer 2

Firstly, and most importantly, you are mistaken in your misconception that the transaction will logically implement some sort of defined loop because your records are held 'consecutively'. In fact the order of your records in your database is undefined and it makes no sense to at all to think of your table storage in this ordered manner. In fact you should try to rid yourself of it completely or else it will lead you into all sorts of traps and bad habits. Try instead to logically think of statements executing as set (in the mathematical sense) operations, not cursor traverses.

It is certainly true that in most relational databases the order in which records will be retrieved by a SELECT without an ORDER BY clause is in insertion order, but this is an implementation issue and in fact should never be relied on in any logic (always use an ORDER BY clause to retrieve data if you care about the order). To emphasis, according to ANSI SQL the order of retrieval of records from a database is undefined without an ORDER BY clause - technically it wouldn't even have to be consistent on sequential execution of the same SELECT statement.

It follows therefore that in order for an UPDATE operation on a relational database to yield consistent results any query must operate as a single transaction. The transaction grabs a snapshot of the records it will update, updates them in a consistent, atomic, manner, then applies the results back to the data. There simply is no logical conception of the SQL looping over records or whatever.