Need help with UPDATE query

J

Jeff

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
 
K

Ken Snell [MVP]

Try using DLookup function:

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

Ofer

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

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)
 
Top