Can update query be used in another query

[sumdumgai]

Not sure if this can be done but here's the problem. I'm updating a field (F1) in a table (T1) by multiplying two other fields (F1 = F2 * F3). Can I then use the updated field (F1) to update another field (F4) in the same table? As in:
F4 = (F1 = F2 * F3)
Thanks.

 

[MarkK]

If F1 = F2 * F3, and F4 = F1, then can't you just do F4 = F2 * F3 again in the same update? Example . . .

UPDATE T1
SET F1 = F2 * F3, F4 = F2 * F3
WHERE F1 <> F2 * F3 OR F1 <> F4

Makes sense?

 

[The_Doc_Man]

This is a fine point, and I agree with MarkK's solution. In a single query, the problem is that you don't know the exact way in which the query implements the set-theory equivalent of what SQL is being commanded to do.

If you use MarkK's method, you have no question as to the values of F1 and F4. But suppose for snorts & giggles that you had done

UPDATE T1 SET F1 = F2 * F3, F4 = F1 etc.etc.

There is no guarantee that F1 has been updated yet when the relevant clause in the statement is executed, so you don't know if you get the new value of F4 or the old one (but I'm betting on the old one...) In general program languages, this is equivalent to saying the record contents are stable; that is, they update as though the operation is monolithic, not sequential. Therefore, if you want to do this in one pass, repeat the computation for each value assertion.

 

[MarkK]

But, it seems like a design problem to store the calculation even once in a row, let alone storing two calculations that are identical. Rather, I would only store the fields F2 and F3 in the table, and then I would write a query that performs the calculations at retrieval time, like . . .

SELECT F2 * F3 As F1, F2, F3, F2 * F3 As F4
FROM T1

. . . so the table only has F2 and F3 as native fields, and the fields F1 and F4 are never stored, but only ever calculated immediately from current data. This guarantees that F1 and F4 are never in conflict with the raw data.

 

[sumdumgai]

Thanks for the quick replies. I didn't present the problem correctly. I should have said that I first want to update F1 to F2*F3, and also update F4 to F5-F1 in the same query. It looks like my query works on a small data set.

UPDATE T1 SET T1.F1 = F2*F3, T1.F4 = F5-F1;

Any reason to suspect this would not always work on a large data set, getting back to Markk's comment about F1 and F4 currency conflicts? In other words, will F1 always be computed before F4 is evaluated?

 

[MarkK]

Well, again, I would not recommend you save those calculations. Store your data raw. Process it at retrieval time so you are certain, beyond a shadow of a doubt, that you are using current data to generate your calculated result. If you store the result of a calculation on your raw data, then you have stored a duplication, and one day a user will edit your raw data, and your recalculation will not fire, and your data will be in conflict with itself. As a data profession, that is totally unacceptable, because the alternative is so easy to engineer: do not store calculations.

I first want to update F1 to F2*F3, and also update F4 to F5-F1

In this case, only F2, F3, and F5 are raw, uncalculated values. So write this query when you need a result . . .

SELECT F2 * F3 As F1, F2, F3, F5 - (F2 * F3) As F4
FROM T1

So in this scheme you never save the results of the calculation to the table, rather, you perform the calculations in a query immediately at retrieval time. This is your best practice for this type of problem.

 

[sumdumgai]

Thanks. Here's what I did following your suggestion. First I created a select query and called it Q1:

SELECT T1.ID, F2*F3 AS F1, T1.F2, T1.F3, F5-(F2*F3) AS F4, T1.F5 FROM T1;

Then, to update the database, I used this update query:

UPDATE T1 INNER JOIN Q1 ON T1.ID=Q1.ID SET T1.F1 = Q1.F1, T1.F4 = Q1.F4;

It seemed to work. See anything wrong?

Thanks.

 

[MarkK]

See anything wrong?

Why do you need to update the database? The SELECT query does all the math and returns the data in exactly the form you need it. One query. You are done.

But now you want to update the database too? So that's a second, unnecessary query. And then you'll need a third query to return the updated data from the table? These steps are a make-work project.

If you have a SELECT query that works, what else is there to do?