SQL Server 2000 and MS Access 2002 UPDATE anomalies

While researching the conversion of a reporting procedure from MS Access
to Oracle, I discovered something most interesting.

Access will allow you to do a cartesian update.

Consider the following 2 tables, NAMES and NAME_LOOKUP:

16:25:13 SQL>select * from names;

        ID NAME
---------- ----------
         1 1 B
         2 2 A
         3 3 C
         4 4 D
         5 5 A

5 rows selected.

16:25:18 SQL>select * from name_lookup;

        ID NAME
---------- ----------
         1 1 A
         1 1 B
         2 2 A
         3 3 A
         3 3 B
         3 3 C
         4 4 A
         4 4 B
         4 4 C
         4 4 D
         5 5 A

11 rows selected.


The following SQL returns a cartesian product as you would expect:

16:26:41 SQL>l
  1  select n.id, nl.name
  2  from names n, name_lookup nl
  3* where n.id = nl.id
16:26:41 SQL>/

        ID NAME
---------- ----------
         1 1 A
         1 1 B
         2 2 A
         3 3 A
         3 3 B
         3 3 C
         4 4 A
         4 4 B
         4 4 C
         4 4 D
         5 5 A

11 rows selected.

What is interesting about MS Access and SQL Server 2000 is that they will
both allow similar SQL logic to perform an update on NAMES:

Access:
UPDATE [names] INNER JOIN name_lookup ON names.id=name_lookup.id SET
[names].name = name_lookup.name;

SQL Server:
UPDATE names SET names.name = name_lookup.name from names INNER JOIN
name_lookup ON (names.id=name_lookup.id);

Both products report 11 rows updated on table NAMES, though there are only
five rows in the table.  Interesting, no?

The row from NAME_LOOKUP used to update the row in NAMES appears
to be completely arbitrary as far as the DB is concerned.  Rearranging the
creation order of the NAME_LOOKUP table seems to indicate that the last
row stored on disk for any particular ID is the one used.  I haven't done
enough testing to prove this.

An equivalent in Oracle would be this:

16:29:18 SQL>l
  1  update names
  2  set name = (
  3     select name
  4     from name_lookup
  5     where id = names.id
  6* )
16:29:19 SQL>/
        select name
        *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

Which of course causes an error.

Modifying the SQL a bit will allow it succeed, though this is probably
not a good way to write UPDATE statements in most cases, and does
not really duplicate what is happening in Access and SQL Server.

16:30:18 SQL>l
  1  update names
  2  set name = (
  3     select max(name) name
  4     from name_lookup
  5     where id = names.id
  6* )
16:30:18 SQL>/

5 rows updated.

I am still fairly new to MS databases, so this was all quite a surprise to me.

Anyone else familiar with this behavior?

If you want to play with this, the MDB file and Oracle scripts are zipped up at:

http://www.jaredstill.com/downloads/cartesian_update.tgz

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l

Other related posts: