THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

Join Effects With UPDATE

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at: http://blogs.lobsterpot.com.au/2016/01/12/join-effects-with-update/

Published Tuesday, January 12, 2016 11:15 AM by Rob Farley

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

RichB said:

Hi...

Curious about:

"If you’re doing an UPDATE, you can only update a single row in that table, no matter what kind of impact your FROM clause might want to have on the base table."

Not entirely sure I've followed what you are saying.

January 13, 2016 7:51 PM
 

Rob Farley said:

Ah - typo. Missed the word "once". You can only update each row one time in an update, even if the query makes it feel like you're doing it multiple times.

January 13, 2016 9:58 PM
 

Gan Davnarrain said:

Very useful and instructive article. Thanks.

January 17, 2016 4:43 AM
 

Robert L Davis said:

Thanks for participating. Great article. I never actually realized that the table in the UPDATE clause could differ from the table in the criteria of the query. Never considered it. I have always used UPDATE x From dbo.Table x JOIN ....

January 19, 2016 8:31 PM
 

Rob Farley said:

Yeah, I think most people have patterns they use, and don't necessarily think about what might happen if they change the pattern. I'm glad you enjoyed it, Robert (and you, Gan!).

January 19, 2016 9:41 PM
 

Dixie S. said:

Regarding using the full table name in the update clause instead of an alias: "Because you have the potential to update a table which isn’t mentioned in the FROM clause, you could find yourself inadvertently updating every row in Production.Product. "

Will you please explain this a little more? -- I'm not sure I understand it.

Is the danger only if you make a mistake and don't type the exact same table name in the From clause as in the Update clause?  I prefer this syntax because to me, it's clearer to have the table name, rather than an alias,  in the Update clause.

But then, to save some typing, I alias the tables in the From clause.

You can imagine how surprised to find my "style" in the unsafe category!

These characteristics of the Update statement are news to me and I appreciate your taking the time to share your insights!

January 20, 2016 9:47 PM
 

Rob Farley said:

Hi Dixie,

What do you do if you need to reference the table twice in your FROM clause? Would that persuade you to update the alias?

By updating the alias, you remove any doubt about which table in the FROM clause is the base table, although you can do it in your style if there is no ambiguity. Hopefully you'll never update a table which isn't mentioned in either the FROM or WHERE clause, but if you always update the alias, you'll be safe from that potential mistake.

If you never make a mistake, any pattern is safe enough, right?

January 20, 2016 11:32 PM
 

Dixie S. said:

You have a point there! I'm not saying I won't make a mistake and I'll be even more careful after reading your post.  I tend to use Update with a join only for the simplest of queries, joining on PK, FK.  Otherwise, I use subqueries or Merge (with a CTE) to prevent the danger of two rows being found in the source to update one row of the target.  

Yours is the best explanation I've read about the various ways of writing and Update statement!

Thank you for your reply.  

January 21, 2016 7:04 PM
 

Krishnamurthy K said:

Hi Rob,

I have query in PDW.when I try update null data(where the column doesn't accept null) it is throwing me "object reference not set" error for few packages and for few packages it is throwing me "column has null value" error.why this is so.

Thank you

August 4, 2016 6:59 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

News

News? Haven't you read my blog?

My Company


Can't find something?

Contact Me

IM: rob_farley@hotmail.com
Twitter: @rob_farley
Skype: rob_farley
E: rob_farley@hotmail.com

MVP (SQL Server)




Certifications








Adelaide SQL UG

Privacy Statement