Bahaviour of SQL update using one-to-many join
ms-access, sql, sql-update
Solution
This is known as a non deterministic query, it means exactly what you have found that you can run the query multiple times with no changes to the query or underlying data and get different results.
In practice what happens is the value will be updated with the last record encountered, so in your case it will be updated twice, but the first update will be overwritten by last. What you have absolutely no control over is in what order the SQL engine accesses the records, it will access them it whatever order it deems fit, this could be simply a clustered index scan from the begining, or it could use other indexes and access the clustered index in a different order. You have no way of knowing this. It is quite likely that running the update multiple times would yield the same result, because with no changes to the data the sql optimiser will use the same query plan. But again there is no guarantee, so you should not rely on a non determinstic query to get deterministic results.
EDIT
To update the value in T1 to the Maximum corresponding value in T2 you can use `DMax`:
UPDATE T1
SET Value = DMax("Value", "T2", "b=" & T1.a);
Problem
Imagine I have two tables, t1 and t2. t1 has two fields, one containing unique values called a and another field called value. Table t2 has a field that does not contain unique values called b and a field also called value. Now, if I use the following update query (this is using MS Access btw): ``` UPDATE t1 INNER JOIN t2 ON t1.a=t2.b SET t1.value=t2.value ``` If I have the following data ``` t1 t2 a | value b | value ------------ ------------ 'm' | 0.0 'm'| 1.1 'm'| 0.2 ``` and run the query what value ends up in t1.value? I ran some tests but couldn't find consistent behaviour, so I'm guessing it might just be undefined. Or this kind of update query is something that just shouldn't be done? There is a long boring story about why I've had to do it this way, but it's irrelevant to the technical nature of my enquiry.