June 21, 2012 at 9:16 pm
Comments posted to this topic are about the item Restricting updates?
June 21, 2012 at 9:31 pm
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
June 22, 2012 at 12:11 am
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
June 22, 2012 at 2:34 am
This was removed by the editor as SPAM
June 22, 2012 at 2:53 am
The subquery in the update confused me. I would use a "case when else" statement instead. It is longer, but for me better readable.
June 22, 2012 at 3:14 am
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!
June 22, 2012 at 3:40 am
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.
June 22, 2012 at 3:44 am
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
June 22, 2012 at 5:15 am
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 😀
June 22, 2012 at 5:17 am
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
June 22, 2012 at 5:26 am
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 ....;
June 22, 2012 at 5:29 am
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
June 22, 2012 at 6:50 am
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 YourTableSET 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. 🙂
June 22, 2012 at 7:02 am
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
June 22, 2012 at 7:24 am
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 33 total)
You must be logged in to reply to this topic. Login to reply