SQL Server 2000 and MS Access 2002 UPDATE anomalies

From: Jared Still <jkstill_at_gmail.com>
Date: Fri, 30 Sep 2005 00:33:03 +0100
Message-ID: <bf46380509291633t70c7e05cv67a14e50b2d25245@mail.gmail.com>

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 Received on Thu Sep 29 2005 - 18:35:18 CDT