Restricting updates?

  • jshahan

    SSCarpal Tunnel

    Points: 4622

    Comments posted to this topic are about the item Restricting updates?

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks for the back to basics question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Very interesting question. I never use this syntax with the correlated subquery, so it would have been nice to give a bit more explanation on that.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This was removed by the editor as SPAM

  • palotaiarpad

    SSCertifiable

    Points: 5524

    The subquery in the update confused me. I would use a "case when else" statement instead. It is longer, but for me better readable.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Koen Verbeeck (6/22/2012)


    Very interesting question. I never use this syntax with the correlated subquery, so it would have been nice to give a bit more explanation on that.

    Logically (actual execution may differ, as long as the results are the same), a correlated subquery is evaluated once for each row in the outer query, substituting the reference to the outer query with the value from the current outer query row.

    In this example (which I hope was designed specifically to test understanding of SQL and was not taken from actual code), the logical processing is as follows:

    1. The query has a simple FROM and no WHERE, so all rows in #tmp1 are processed. The alias (t1) means that in the context of the query, the table is temporarily renamed to t1, so references to #tmp1 become invalid. (The official ANSI specification doesn't involve temporarily renaming the table, but making a copy of the table's data under the new name - but for the non-ANSI update ... from construction, that would not work as the updates have to flow to the original table and not to the copy).

    2. The rows in t1 are processed one by one. The order is irrelevant. For this discussion, let's assume they are processed in order of ascending EnrollmentKey value.

    3. For the first row (101), the subquery is evaluated. t1.Enrollmentkey references the outer query, so is replaced with its value. The subquery now reads "(select '5/2/2012' where 101 = 102)". There is no FROM clause, so one zero-column dummy row is assumed (I'm not sure if ANSI allows this; I know Oracle and DB2 don't -you have to add FROM DUAL (for Oracle) or FROM SYSIBM.SYSDUMMY (for DB2) to mimic this behaviour-). The WHERE clause evaluates to false, so this dummy row is kicked out.

    The subquery returns an empty set, which is converted to NULL. The outer query will now set PacketDate to NULL for the first row.

    4. For the second row (102), the subquery is evaluated again. t1.Enrollmentkey is replaced with its value, so the subquery now reads "(select '5/2/2012' where 102 = 102)". This evaluates to true, so the dummy row is kept. For each row in this result (all one of them), the SELECT clause will return the constant character value '5/2/2012'.

    The subquery returns an set consisting of one row with one column, holding the character data '5/2/2012'. The outer query will implicitly convert this to datetime (resulting in either February 5th 2012 or May 2nd 2012, depending on locale settings - or maybe even in a runtime error in some cultures!) and set PacketDate to that value for the second row.

    5. For the third row (103), the subquery is evaluated again. t1.Enrollmentkey is replaced with its value, so the subquery now reads "(select '5/2/2012' where 103 = 102)". This evaluates to false, so the dummy row is kicked out.

    The subquery returns an empty set, which is converted to NULL. The outer query will now set PacketDate to NULL for the third row.

    It is important for any SQL developer to understand correlated subqueries, as there are many situations where they are a great tool. It is also important to understand that the scenario of this QotD is no such situation!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • keith.fearnley

    Mr or Mrs. 500

    Points: 587

    Thanks for the explanation.

    I'd assumed that the inner SELECT without a FROM would fail syntax.

    I am now enlightened, so the QOD has done its job for me.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Hugo, thanks for the very lengthy and detailed explanation. I already figured out how it worked, but I found the original explanation "there isn't a WHERE clause" a bit lacking. There is a WHERE clause in the query, but in the correlated subquery, not in the outer query.

    Anyhow, thanks again for your detailed explanation 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Gazareth

    One Orange Chip

    Points: 27737

    Nice question, thanks.

    I'd like to start a quick poll: who's updated the most rows in a live table accidentally by not including a where clause in their update statement?

    (Doesn't count if it was within a rollback)

    I'll open at 500 😀

  • Andre Guerreiro

    SSCertifiable

    Points: 7319

    Thanks for the question and thanks to Hugo for the detailed explanation.

    This could be a useful code to set some fields from a a few restricted rows to some value and the rest to NULL in a single UPDATE statement.

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    codebyo (6/22/2012)


    Thanks for the question and thanks to Hugo for the detailed explanation.

    This could be a useful code to set some fields from a a few restricted rows to some value and the rest to NULL in a single UPDATE statement.

    I have to disagree. Chances are that you won't be able to understand your code yourself if you revisit it after a few months. For your colleagues, it will be even worse.

    As palotaiarpad already commented: a CASE is much better in such a case:

    UPDATE YourTable

    SET SomeColumn = CASE WHEN (....) THEN SomeValue ELSE NULL END

    WHERE ....;


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Ron McCullough

    SSC Guru

    Points: 63877

    Koen Verbeeck (6/22/2012)


    Hugo, thanks for the very lengthy and detailed explanation. I already figured out how it worked, but I found the original explanation "there isn't a WHERE clause" a bit lacking. There is a WHERE clause in the query, but in the correlated subquery, not in the outer query.

    Anyhow, thanks again for your detailed explanation 🙂

    +1

    Hugo

    Many, many thanks for your explanation

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Dana Medley

    SSCertifiable

    Points: 6764

    Hugo Kornelis (6/22/2012)


    codebyo (6/22/2012)


    Thanks for the question and thanks to Hugo for the detailed explanation.

    This could be a useful code to set some fields from a a few restricted rows to some value and the rest to NULL in a single UPDATE statement.

    I have to disagree. Chances are that you won't be able to understand your code yourself if you revisit it after a few months. For your colleagues, it will be even worse.

    As palotaiarpad already commented: a CASE is much better in such a case:

    UPDATE YourTable

    SET SomeColumn = CASE WHEN (....) THEN SomeValue ELSE NULL END

    WHERE ....;

    + 1. This was pretty much everything I had to say about it. Took the words right out of my mouth. Thanks Hugo. 🙂



    Everything is awesome!

  • Thomas Abraham

    SSChampion

    Points: 10761

    Arriving later on the scene than others, primarily due to time zone (and the fact that I don't get up at 3 AM to do the QotD), all I can say is:

    Thanks for the question - I found it very interesting, even if I would never use the type of code shown. This is a very nice use of a reasonably short piece of code to demonstrate a principle.

    Thanks to Hugo for the time and effort to post the detailed explanation.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • sknox

    SSChampion

    Points: 12292

    Gazareth (6/22/2012)


    Nice question, thanks.

    I'd like to start a quick poll: who's updated the most rows in a live table accidentally by not including a where clause in their update statement?

    (Doesn't count if it was within a rollback)

    I'll open at 500 😀

    Last time I did that, I messed up approximately 2,000 rows :Whistling:. Had to restore that table from backup. Fortunately, that was about 6 years ago.

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply