Re: SQL Server 2000 and MS Access 2002 UPDATE anomalies
- From: Jared Still <jkstill@xxxxxxxxx>
- To: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- Date: Thu, 29 Sep 2005 22:37:16 -0700
Not fully cartesian, as that would return 55 rows.
The value used to do the lookup does not fully qualify
the key in the NAME_LOOKUP table, causing several
rows to be returned in many cases, so maybe cartesian
is the wrong word to use.
The tables are somewhat contrived to make a simple example,
but I took this from a real life scenario.
I was not terribly surprised that Access allowed this, but
was surprised that SQL Server would.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
On 9/29/05, Allen, Brandon <Brandon.Allen@xxxxxxxxxxx> wrote:
> I don't believe that is a cartesian product - since you have the join
> condition "where n.id = nl.id". If you removed that, then you would have a
> cartesian, which would result in 5*11=55 rows returned. Right, or am I
> missing something?
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jared Still
>
>
>
> 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>/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
