a simple update, inner join statement??? or NOT

[steveevets]

I am trying to perform what seems like a simple query using Access (Office 2000); as follows;

UPDATE wrk_keylist AS wkl
INNER JOIN keylist AS kl
ON kl.[list #] IS NOT Null
SET wrk_keylist.[old list #] = keylist.[list #]
WHERE keylist.keyword=wrk_keylist.keyword And keylist.[site name]=wrk_keylist.[site name] AND kl.[list #] >= 1;

error returned is "Join not supported"

 

[steveevets]

and ps -

the where statement is actually:

WHERE kl.keyword=wkl.keyword And kl.[site name]=wkl.[site name];

** using the aliases

 

[PHV]

UPDATE wrk_keylist AS wkl
INNER JOIN keylist AS kl
ON kl.keyword=wkl.keyword And kl.[site name]=wkl.[site name]
SET wrk_keylist.[old list #] = keylist.[list #]
WHERE kl.[list #] >= 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

Do you mean:

[tt]UPDATE wrk_keylist AS wkl
INNER JOIN keylist AS kl
ON (k1.keyword=wrk1.keyword) And (k1.[site name]=wrk1.[site name])
SET wrk1.[old list #] = k1.[list #]
WHERE kl.[list #] IS NOT Null AND kl.[list #] >= 1;[/tt]

 

[PHV]

Remou, if kl.[list #] >= 1 then it can't be null ;-)

 

[Remou]

Sigh