CODEWITHSUNDEEP
sqldatabasesql-update
Paulo André Haacke
Paulo André Haacke

Reputation: 402

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.

Upvotes: 1

Views: 88

Answers (3)

Matt Gibson
Matt Gibson

Reputation: 38238

If this worked—and I'd not expect it to on every database—this might help illustrate what's going on. Y is just an alias of Z. This table represents the joining of the tables during the update and the final results:

Z values    Y (alias) joined on Z + 1   Update Value  Z following update
========    =========================   ============  ==================
            1                           NO MATCH
1           2                           3             3
2           3                           4             4
3           4                           5             5
4                                       NO MATCH      4
                                                      (SUM: 16)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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.

Upvotes: 2

etsa
etsa

Reputation: 5060

I hope this could help (made on MSSQL): The first SELECT will show you original values the the update will use.

I made UPDATE inside transaction / rollback so it will not change the table. You can delete BEGIN TRAN and ROLLBACK TRAN if you want to change your data table.

CREATE TABLE TZ (VALUE INT)
    INSERT INTO TZ VALUES (1),(2),(3),(4)

    SELECT Z.VALUE AS Z_VALUE, Y.VALUE AS Y_VALUE
     FROM TZ Z 
     INNER JOIN TZ Y  ON Y.VALUE=Z.VALUE +1
     ;

    BEGIN TRAN
    UPDATE Z SET VALUE=Y.VALUE+1
    FROM TZ Z 
    INNER JOIN TZ Y ON Y.VALUE=Z.VALUE +1
    ;
    SELECT * FROM TZ;
    SElECT SUM(VALUE) AS TOT FROM TZ;
    ROLLBACK TRAN

Output first SELECT:

Z_VALUE, Y_VALUE
1      , 2   
2      , 3
3      , 4

Output of SELECT after UPDATE:

VALUE
3
4
5
4

So, SUM is actually 16

Upvotes: 2

Related Questions