Update with Inner Join

G

Guest

If I have
UPDATE T1 INNER JOIN T2 ON T1.t1 = T2.t1 SET T1.t2 = [T2]![t2];
and T2 returns multiple rows on the inner join, which row's t2 value Access
will use to do the update? The first? Last? Or Access will choose randomly?
 
K

Ken Snell [MVP]

Did you try it on a copy of your database? My guess is that ACCESS will tell
you it cannot run this query because it cannot identify the record/table
correctly.

What type of data are in both tables? Is there just a single record in T2
with the same value of t1 as in T1?
 
G

Guest

Thanks for replying.

Yes I tried. It allowed me to do it (surprise!), that's why I asked here
because it shouldn't. With the simple tables I had, it used the first record
(I have 3 records in T2 with the same value of t1 as in T1, it used the
first t2 value of the three, but I'm not sure that's the norm). I was using
Access 2000.

Ken Snell said:
Did you try it on a copy of your database? My guess is that ACCESS will tell
you it cannot run this query because it cannot identify the record/table
correctly.

What type of data are in both tables? Is there just a single record in T2
with the same value of t1 as in T1?

--

Ken Snell
<MS ACCESS MVP>

newgun said:
If I have
UPDATE T1 INNER JOIN T2 ON T1.t1 = T2.t1 SET T1.t2 = [T2]![t2];
and T2 returns multiple rows on the inner join, which row's t2 value
Access
will use to do the update? The first? Last? Or Access will choose
randomly?
 
K

Ken Snell [MVP]

It appears that Jet treats the "many" side of the join as a "Top 1" type
query in your situation, where the sort order is not defined and thus Jet
will select the first record it "gets" from the "many" side. If you have
multiple records in T2 with same value of t1, how would you decide which one
to use for the update if you were doing it manually? We'll need to tell
ACCESS how to make that same decision.

Provide some more information about what you want to do, and we can suggest
a change to the query.

--

Ken Snell
<MS ACCESS MVP>

newgun said:
Thanks for replying.

Yes I tried. It allowed me to do it (surprise!), that's why I asked here
because it shouldn't. With the simple tables I had, it used the first
record
(I have 3 records in T2 with the same value of t1 as in T1, it used the
first t2 value of the three, but I'm not sure that's the norm). I was
using
Access 2000.

Ken Snell said:
Did you try it on a copy of your database? My guess is that ACCESS will
tell
you it cannot run this query because it cannot identify the record/table
correctly.

What type of data are in both tables? Is there just a single record in T2
with the same value of t1 as in T1?

--

Ken Snell
<MS ACCESS MVP>

newgun said:
If I have
UPDATE T1 INNER JOIN T2 ON T1.t1 = T2.t1 SET T1.t2 = [T2]![t2];
and T2 returns multiple rows on the inner join, which row's t2 value
Access
will use to do the update? The first? Last? Or Access will choose
randomly?
 
G

Guest

Hi Ken,

Thanks for the reply. My understand by rewriting the query most of the
time the multi-records situation can be eliminated but I was just curious if
there was a set of rules for Jet to follow if the Many side indeed returns
multiple records (I couldn't find this in any of the docs I have searched).
By the way, some of the other DBMS (like Oracle) would treat it as an error
if the Many side actually returned multiple records, AFAIK.

Ken Snell said:
It appears that Jet treats the "many" side of the join as a "Top 1" type
query in your situation, where the sort order is not defined and thus Jet
will select the first record it "gets" from the "many" side. If you have
multiple records in T2 with same value of t1, how would you decide which one
to use for the update if you were doing it manually? We'll need to tell
ACCESS how to make that same decision.

Provide some more information about what you want to do, and we can suggest
a change to the query.

--

Ken Snell
<MS ACCESS MVP>

newgun said:
Thanks for replying.

Yes I tried. It allowed me to do it (surprise!), that's why I asked here
because it shouldn't. With the simple tables I had, it used the first
record
(I have 3 records in T2 with the same value of t1 as in T1, it used the
first t2 value of the three, but I'm not sure that's the norm). I was
using
Access 2000.

Ken Snell said:
Did you try it on a copy of your database? My guess is that ACCESS will
tell
you it cannot run this query because it cannot identify the record/table
correctly.

What type of data are in both tables? Is there just a single record in T2
with the same value of t1 as in T1?

--

Ken Snell
<MS ACCESS MVP>

If I have
UPDATE T1 INNER JOIN T2 ON T1.t1 = T2.t1 SET T1.t2 = [T2]![t2];
and T2 returns multiple rows on the inner join, which row's t2 value
Access
will use to do the update? The first? Last? Or Access will choose
randomly?
 
K

Ken Snell [MVP]

Jet will return it as an error in situations where Jet expects a single
record on the "many" side of the join, but in your case Jet is not aware
that there would be a problem -- it's asked to get a value from the "many"
side of the join, it finds one, it doesn't have to be unique, and it uses
it. I agree it may not be good to allow this, but that is how it appears to
work.

The rule regarding the order of how Jet would obtain the many records is
completely at the mercy of the DB engine unless you put constraints on the
order (by an ORDER BY or by using a totals query or some other method).
--

Ken Snell
<MS ACCESS MVP>



newgun said:
Hi Ken,

Thanks for the reply. My understand by rewriting the query most of the
time the multi-records situation can be eliminated but I was just curious
if
there was a set of rules for Jet to follow if the Many side indeed returns
multiple records (I couldn't find this in any of the docs I have
searched).
By the way, some of the other DBMS (like Oracle) would treat it as an
error
if the Many side actually returned multiple records, AFAIK.

Ken Snell said:
It appears that Jet treats the "many" side of the join as a "Top 1" type
query in your situation, where the sort order is not defined and thus Jet
will select the first record it "gets" from the "many" side. If you have
multiple records in T2 with same value of t1, how would you decide which
one
to use for the update if you were doing it manually? We'll need to tell
ACCESS how to make that same decision.

Provide some more information about what you want to do, and we can
suggest
a change to the query.

--

Ken Snell
<MS ACCESS MVP>

newgun said:
Thanks for replying.

Yes I tried. It allowed me to do it (surprise!), that's why I asked
here
because it shouldn't. With the simple tables I had, it used the first
record
(I have 3 records in T2 with the same value of t1 as in T1, it used
the
first t2 value of the three, but I'm not sure that's the norm). I was
using
Access 2000.

:

Did you try it on a copy of your database? My guess is that ACCESS
will
tell
you it cannot run this query because it cannot identify the
record/table
correctly.

What type of data are in both tables? Is there just a single record in
T2
with the same value of t1 as in T1?

--

Ken Snell
<MS ACCESS MVP>

If I have
UPDATE T1 INNER JOIN T2 ON T1.t1 = T2.t1 SET T1.t2 = [T2]![t2];
and T2 returns multiple rows on the inner join, which row's t2 value
Access
will use to do the update? The first? Last? Or Access will choose
randomly?
 
M

Michel Walsh

Hi,



Jet will use all the values, making all the updates, and keeping the last
on, as if you would in:

Dim x As integer
x=2
x=4
x=3


letting you with x=3, at the end, after having made the first two
assignments.


You can check that with concatenation of strings:


..... SET t1.t2 = (t1.t2 + ", " ) & t2.t2


as example.



Hoping it may help,
Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top