Results 1 to 10 of 10
  1. #1
    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 08:43.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    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

  3. #3
    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 13:18.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    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

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,947
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    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

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,947
    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.

  8. #8
    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!

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    "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

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •