While answering a test i faced the following question, which i wasn't able to solve:
Given the following table Z and query:
| Value |
| 1 |
| 2 |
| 3 |
| 4 |
SET VALUE = Y.VALUE + 1
FROM Z AS Y
WHERE Y.VALUE = Z.VALUE + 1;
SELECT SUM(VALUE) FROM Z;
One database where the code will work is Postgres. According to RexTester, this is indeed the answer.
The reason should be because you are adding "2" to each matching Z value: z = y.value + 1 = z.value + 1 + 1 -- but the fourth value does not match. Postgres generates the following:
value 1 4 2 3 3 4 4 5
This is the same data just in a different order.
With a similar statement, SQL Server does the right thing:
UPDATE Z SET val = Y.val + 1 FROM Z, Z AS Y WHERE Y.val = Z.val + 1;
(I am using the dreaded comma in a
FROM clause to keep the two statements as similar as possible.)
val 1 3 2 4 3 5 4 4
The two result sets are the same, they are just in a different order.