If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Please Help - Correct Update Statement to use

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 1
Please Help - Correct Update Statement to use

Good Morning,

Sorry to bother you guys with something so trivial but I am new to SQL.

I am running the query as shown below..

SELECT * FROM organisation WITH (NOLOCK) , member, membership_history membership_hist382 WITH (NOLOCK) , member member382 WITH (NOLOCK) , v_member_history v_member_histor382 WITH (NOLOCK) WHERE member.member_ref = 86486 and member.organisation_ref = organisation.organisation_ref and membership_hist382.member_plan_ref = 197 and membership_hist382.history_status = 251 and v_member_histor382.member_ref = member382.member_ref
AND v_member_histor382.member_history_ref = membership_hist382.member_history_ref
AND member382.organisation_ref = organisation.organisation_ref


I want to run an update statement that will change membership_hist382.history_status to equal 249 instead of 251. Can anyone tell me how to format the update statement as I have been hitting a few issues.

Obviously I only want the change to affect the record returned in the search criteria.

Thanks in advance for your advice.

Last edited by MJB; 01-25-13 at 07:43.
Reply With Quote
  #2 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,522
First drop all the NOLOCK hints.
Second rewrite your query using proper JOIN syntax.
Third, change your query from SELECT to UPDATE.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Location: Logan, Utah
Posts: 162
Here is the Select statement reformatted, using inner joins and abbreviations for the table names and reordered to match what I think you are looking for:

Code:
SELECT * 
  FROM Membership_History mh382
 INNER JOIN Member m1 on m1.Member_Ref = 86486
 INNER JOIN Organisation Org on Org.Organisation_Ref = m1.Organisation_Ref
 INNER JOIN Member m2 on m2.Organisation_Ref = Org.Organisation_Ref
 INNER JOIN v_Membership_History vmh382 on vmh382.Member_ref = m2.Member_Ref and vmh382.Member_History_Ref = mh382.Member_History_Ref
 WHERE mh382.member_plan_ref = 197 and mh382.History_Status = 251
Unless you really, really want all fields, never use SELECT *. Specify what columns you are after. Use aliases. Too much typing with these overly long table and column names.

As was stated earlier, after creating a proper select, it becomes trivial to convert it to an update.

Code:
UPDATE mh382 
   SET mh382.History_Status = 249
  FROM Membership_History mh382
 INNER JOIN Member m1 on m1.Member_Ref = 86486
 INNER JOIN Organisation Org on Org.Organisation_Ref = m1.Organisation_Ref
 INNER JOIN Member m2 on m2.Organisation_Ref = Org.Organisation_Ref
 INNER JOIN v_Membership_History vmh382 on vmh382.Member_ref = m2.Member_Ref and vmh382.Member_History_Ref = mh382.Member_History_Ref
 WHERE mh382.member_plan_ref = 197 and mh382.History_Status = 251
This should be a starting point you can use to improve your SQL.

HTH

Last edited by LinksUp; 01-26-13 at 12:18.
Reply With Quote
  #4 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,522
Quote:
Originally Posted by LinksUp View Post
Unless you really, really want all fields, never use SELECT *.
Absolutely.

Quote:
Originally Posted by LinksUp View Post
Specify what columns you are after.
Excellent advice.

Quote:
Originally Posted by LinksUp View Post
Use aliases. Too much typing with these overly long table and column names.
NOOOOOOOOOOO!!!!!!!!!!!
Do NOT use gratuitous aliases! They serve only to obfuscate code.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #5 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,429
While blindman apparently objects to aliases, I use them most of the time. Granted that a large percentage of my queries require aliases, I also use aliases just to reduce the typing/reading/parsing time too.

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #6 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,522
I object to gratuitous aliases.
I object to meaningless aliases.

In the sample code above, "m1", "m2", "Org", and "mh382" serve no purpose.

My question would be: if you find yourself constantly aliasing table names, why didn't you name them that way to begin with?
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #7 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,429
Quote:
Originally Posted by blindman View Post
My question would be: if you find yourself constantly aliasing table names, why didn't you name them that way to begin with?
I name things in the schema to be verbose, descriptive, consistant, and locale specific. For example, in a Greek database the list of clients would be stored in a table named πελάτες which makes perfect sense.

I name things within code (such as a SQL statement) to be concise, easily understood, and free of excess verbiage). In my queries, the alias for πελάτες would be either c or cust.

While both of the name domains resolve to the same objects, they have very different goals so using a single naming standard doesn't work for me.

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Location: Logan, Utah
Posts: 162
Quote:
Originally Posted by blindman View Post
NOOOOOOOOOOO!!!!!!!!!!!
Do NOT use gratuitous aliases! They serve only to obfuscate code.
It maybe because I didn't write the original code, but when I was looking at it and trying to sort what goes where, those overly long table name and column names did more to confuse me and obfuscate what what was going on then using aliases. For me, in this query, it became a necessity to provide an alias just to keep things straight and cut WAY down on the typing!
Reply With Quote
  #9 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,522
"organisation", "member", "membership_history", "v_member_history"

None of these are overly verbose. They each represent the minimum information necessary to accurately (presumably) convey the contents and nature of the data set.

Does "mh382.History_Status" indicate anything on its own? No. We have to jump back down to the table joins to find out what it means. Therefore, descriptive information has been lost by the use of this alias.

Aliasing is necessary for the "member" table, since it is mentioned twice, but the choice of alias should be guided by clarity, not brevity.

I'd also quibble with the use of underscore characters (a minor point) and the "v_" prefixing (a much larger annoyance).
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #10 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,522
I apologize if my response came off as harsh, LinksUp.
Your advice was sound, but you happened to step on a pet peeve of mine...
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On