Paulo André Haacke Paulo André Haacke - 1 month ago 12
SQL Question

SQL UPDATE Query Using FROM

While answering a test i faced the following question, which i wasn't able to solve:

Given the following table Z and query:

Table Z:
| Value |
---------
| 1 |
| 2 |
| 3 |
| 4 |
---------


Query:

UPDATE Z
SET VALUE = Y.VALUE + 1
FROM Z AS Y
WHERE Y.VALUE = Z.VALUE + 1;

SELECT SUM(VALUE) FROM Z;


The question asks for the result of this query execution. The question doesn't mention a specific SQL language.

The CORRECT answer is 16.

I don't know how this query can achieve this result. I wasn't even able to execute this query in a real environment, it complains about some syntax error near "FROM".

1 - Do you guys know how this query works?
2 - How could i proceed in order to execute this query?

P.S. I had a hard time trying to find some information about the FROM clause inside the UPDATE query.

Answer Source

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

It returns:

    val
1   3
2   4
3   5
4   4

The two result sets are the same, they are just in a different order.