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.

First drop all the NOLOCK hints.
Second rewrite your query using proper JOIN syntax.
Third, change your query from SELECT to UPDATE.

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:

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.

This should be a starting point you can use to improve your SQL.

HTH

Absolutely.

Excellent advice.

NOOOOOOOOOOO!!!!!!!!!!!
Do NOT use gratuitous aliases! They serve only to obfuscate code.

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

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?

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

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!

"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).

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...