Very Computer

1. Access bug in update query when multiple rows join

I believe there is a bug Access 97.
If an update query that involves a join that returns multiple matching
rows the update is done, even though the query does
not determine a unique update value.

Here is a simple test case.  The table t1 has one row.
The column named j (mnemonic for join) has the value 'a'
and the column v (for value) is what we want to update.
Table t2 is also trivial: 2 rows and 2 columns as follows:
j = 'a', v = 'foo'
j = 'a', v = 'bar'
The query below was copied from the SQL view in Access.
UPDATE t1 INNER JOIN t2 ON t1.j = t2.j SET t1.v = [t2].[v];
Both rows from t2 join to the one row of t1.
I believe the correct behavior is to reject this update query
as invalid, because the update is not "deterministic".
This is in fact what Informix does.
Access updates t1.v to one of the values in t2.v.
Is there a place in the SQL standard that states what the
behavior should be?

I found no information about this in the knowledge base, although I
may have searched on the wrong terms.

Hopefully helpfully yours,
Steve
--

Fidelity Investments   82 Devonshire St. R24D    Boston MA 02109
There is nothing so practical as a good theory.  Comments are by me,
not Fidelity Investments, its subsidiaries or affiliates.

2. row count in flat file

3. single row updates effect multiple rows....

4. Help files

5. HELP! UPDATE single-row subquery returns multiple rows

6. select the x-th to the y-th rows from one table -jsl

7. How to update multiple rows using UPDATE

8. Help ODBC Direct

9. UPDATE trigger - multiple rows affected by update

10. column in multiple rows to multiple columns in one row

11. Multiple db.executes in a row dont work.

12. Using DTS to update multiple tables from a single input row