About the Update statement difference between Excel97 and SQL Server2000

[Arron]

In Excel 97+dao360.dll
I write some VBA code.And I use sql statement :"UPDATE
TMP1 inner join tmp2 on tmp1.id=tmp2.id set
tmp1.name=tmp2.name"
but it returns error message.
/Join expression not supported. (Error 3296)/

In SQL Server ,I write "Update tmp1 set
tmp1.name=tmp2.name from tmp2 where tmp1.id=tmp2.id"
to gain my goal.And it's correct.

My question is :How should I change my sql statement in
excel97 to gain the same goal?

Thank you~~~

ps.
Possible causes:

Your SQL statement contains multiple joins in which the
results of the query can differ, depending on the order in
which the joins are performed. You may want to create a
separate query to perform the first join, and then include
that query in your SQL statement.
The ON statement in your JOIN operation is incomplete or
contains too many tables. You may want to put your ON
expression in a WHERE clause.

 

[Dianne Butterworth]

Try (not tested):

UPDATE tmp1 SET name =
( SELECT name FROM tmp2
WHERE tmp2.id = tmp1.id )