how do i make this work?  do i need a join?
this works in access:

UPDATE tblCaseProfiles, tblProviderInvestigatorProfile SET
tblCaseProfiles.NewInvestigator = tblProviderInvestigatorProfile.PI_KEY
WHERE tblCaseProfiles.Investigator=tblProviderInvestigatorProfile.IP_KEY;

hi

probably you can try this

UPDATE tblCaseProfiles
SET
tblCaseProfiles.NewInvestigator = tblProviderInvestigatorProfile.PI_KEY
FROM tblCaseProfiles
INNER JOIN tblProviderInvestigatorProfile
ON tblCaseProfiles.Investigator=tblProviderInvestigatorProfile.IP_KEY


Show quoteHide quote

">
gosh you're fast today!

i got it working much the same as you but with a where clause instead of
inner join.

1) Stop using that silly "tbl-" prefixes in violation of ISO-11179
2) An UPDATE clause has one and only one base table name
3) Never use the proprietary UPDATE.. FROM.. syntax; it is
unpredictable and hides cardinality violatins
4) A data element has one and only one name.  A data element is never
named for where or how it is used, so why did you name an investigaror
both "investigaror" and "pi_key"?
5) Here is a direct translation to Standard SQL

UPDATE CaseProfiles
SET new_investigator
    = (SELECT P.investigator
         FROM ProviderInvestigatorProfiles AS P
        WHERE CaseProfiles.investigator = P.investigator);

Hi Celko,

     Using Corelated subquery involves more system overhead . Please
correct me if the assumption is wrong.
     Using properitery Syntax is not correct , but it gives better
performance .


With warm regards
Jatinder Singh

Alter table / update... set - strange problem
Newbee question about permissions and sp_OACreate
Stored Procs vs VIEWS: Seeking Comparison
How to find gaps in sequential key
boolean programming