CODEWITHSUNDEEP
sqlms-accesssql-update
Felipe
Felipe

Reputation: 283

Update more than one field in a table, having one field depending on the other

I am having problems to update 3 values from a table.

I am using the following code:

UPDATE tblOperation 
SET  
tblOperation.DateDifference = DateDiff("d",[tblOperation].[Date],[tblOperation].[FinalDate]), 
tblOperation.Tax = tblOperation.Value*tblOperation.Percentage/3000*tblOperation.DateDifference, 
tblOperation.FinalValue = tblOperation.Value-tblOperation.Tax;

The problem is that it is updating only the DateDifference value. To update the other fields, I have to run the code two more times...

How to do it in a single run?

Upvotes: 0

Views: 63

Answers (1)

swestfall
swestfall

Reputation: 411

It won't work in one query like that because the second and third updates are using the value of the previous columns. Since they are all updated in a Set you have to calculate them as well. You can't just use the column name. Basically it doesn't update each column synchronously.

UPDATE tblOperation 
    SET  
    tblOperation.DateDifference = DateDiff("d",[tblOperation].[Date],[tblOperation].[FinalDate]), 
    tblOperation.Tax = tblOperation.Value*tblOperation.Percentage/3000*(DateDiff("d",[tblOperation].[Date],[tblOperation].[FinalDate])), 
    tblOperation.FinalValue = tblOperation.Value-(tblOperation.Value*tblOperation.Percentage/3000*(DateDiff("d",[tblOperation].[Date],[tblOperation].[FinalDate])));

Upvotes: 1

Related Questions