INNER JOIN and UPDATE for Jet

[JeffCarlin]

I'm trying to update an Access97 table (tblA) with data from a 2nd table (tblB) using an INNER JOIN statement. This is what I'm using:

UPDATE tblA INNER JOIN tblB ON tblA.ID_field = tblB.ID_field SET tblA.Field1=tblB.Field1, tblA.Field2=tblB.Field2

Can't get it to work. Anyone??

 

[Brawn]

I'm not sure if this is what your looking for.

UPDATE tblA SET tblA.Field1 = [tblB].[Field1],
tblA.Field2 = [tblB].[Field2]
WHERE (((tblA.ID_field )=[tblB].[ID_field ]));

Brawn

 

[Dzidze]

You can't do it.

What you can do though is get that value in a variable and then update your table using that same variable.

Have fun...

 

[JeffCarlin]

Hi Brawn and Dzidze:

You're both right, of course. However, that would mean running that query once for each record. For example, spinning thru tblB and running the query, then "movenext", etc.

What I'm trying to do is to run a single query that would update the entire tblA. That's the reason for the "INNER JOIN".

Thanks.

 

[Sashanan]

Actually I'm pretty sure Brawn's solution will do the entire table at once. I've used that exact same query for a similar issue last week and it worked fine (although that wasn't on Jet but on SQL Server).
"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[Sashanan]

Just checked my own original query again and noticed one difference with the one Brawn suggested. I included a FROM clause:

UPDATE tbla
SET tblA.field1 = tblB.field1, tblA.field2 = tblB.field2
FROM tblA, tblB
WHERE tblA.id_field = tblB.id_field
"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[VogonPoet]

Your code should work. If tblb.ID_Field is not a unique key, you may need to change the update statement to 'UPDATE DISTINCTROW'. Access is kind of funny this way.