Need help with UPDATE query

[Guest]

Hello,

I want to be able to Update one record based on another in the same table.

So for example I want to update the "ChangedDate" of the record that has the
FairValueID=92 with the "ChangedDate" of the record that has the
FairValueID=89

I tried this following SQL but I am getting an error (Operation must use an
updatable query. (Error 3073))

UPDATE tblFairValueData
SET ChangedDate = (SELECT ChangedDate FROM tblFairValueData WHERE
FairValueID=89)
WHERE FairValueID=92;

What am I doing worng ? or what is the corret syntax to do what I am asking.

Any help would be greatly appreciated.

Thank you,
Jeff

 

[Ken Snell [MVP]]

Try using DLookup function:

UPDATE tblFairValueData
SET ChangedDate = DLookup("ChangedDate",
"tblFairValueData","FairValueID=89")
WHERE FairValueID=92;

 

[Guest]

Try this
UPDATE tblFairValueData
SET ChangedDate = DLookUp("ChangedDate","tblFairValueData","FairValueID=89")
WHERE FairValueID=92;

 

[Van T. Dinh]

Try also (untested):

UPDATE tblFairValueData AS Dest, tblFairValueData As Src
SET Dest.ChangedDate = Src.ChangedDate
WHERE (Dest.FairValueID = 92)
AND (Src.FairValueID = 89)