SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restricting updates?


Restricting updates?

Author
Message
jshahan
jshahan
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1028 Visits: 2159
Comments posted to this topic are about the item Restricting updates?
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64893 Visits: 18570
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

Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60515 Visits: 13297
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16697 Visits: 7413
Good back-to-basics question, thanks.

However, I must agree with Koen regarding the explanation.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
palotaiarpad
palotaiarpad
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2416 Visits: 804
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
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18477 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
keith.fearnley
keith.fearnley
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 224
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
Koen Verbeeck
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60515 Visits: 13297
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 Smile


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7558 Visits: 6045
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 :-D
Andre Guerreiro
Andre Guerreiro
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2217 Visits: 1515
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search