Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

update version of SQL update question (sorry for confusion)

Posted on 2007-07-30
3
Medium Priority
?
185 Views
Last Modified: 2010-03-20
This is an updated version of questions posted by me before
(SQL update/set more than one column 7/30/07 and SQL update logic 7/28/07).
I hope this version is clear.
I have four tables:
Table1, Table2, Table3, and Table4
Table1 and Table2 have common column id. Table2 has columns that can be matched with columns in Table3 and Table4.
I need to join Table1 and Table2 and update column in Table1 depending on different conditions
(1)      when Table2.colA = Table3.ColA and Table2.colB = Table3.ColB Then 1 (set to 1 if a match and dont check anything else)
(2)      when Table2.colA=Table4.ColA and Table2.colB = Table4.ColB  Then 2 (set to 2 if a match and dont check anything else)
else  3

If there is no match for when (1) and when (2) then set the value to 3.
0
Comment
Question by:jgordin
  • 3
3 Comments
 
LVL 14

Accepted Solution

by:
wsh2 earned 1500 total points
ID: 19596380
For MsAccess Jet:
-------------------------------------------------------------------------------------------------------------
UPDATE
    (( [Table1] T1
 INNER JOIN
       [Table2] T2
    ON T1.[Key] = T2.[Key]
     )
  LEFT JOIN
       [Table3] T3
    ON T2.[ColA] = T3.[ColA]
   AND T2.[ColB] = T3.[ColB]
     )
 LEFT JOIN
       [Table4] T4
    ON T2.[ColA] = T4.[ColA]
   AND T2.[ColB] = T4.[ColB]
   SET T1.[MyField] = IIF(ISNULL(T3.[ColA], 2, 1)
 WHERE
     ( ISNULL(T3.[ColA]) <> True  
   AND ISNULL(T3.[ColB]) <> True  
     ) OR
     ( ISNULL(T4.[ColA]) <> True  
   AND ISNULL(T4.[ColB]) <> True  
     )
-------------------------------------------------------------------------------------------------------------
0
 
LVL 14

Expert Comment

by:wsh2
ID: 19728081
jgordin..

Respectfully, why the B grade?..

What part of your question did I not respond to? and if I did, what additional comment of yours did I miss? Quite frankly, what more could I have done with the information you provided?
0
 
LVL 14

Expert Comment

by:wsh2
ID: 19728105
jgordin.. please take a moment and read the grading guidelines:

http://www.experts-exchange.com/help.jsp#hi73

What's the right grade to give?

Although we use an A-C scale here at Experts Exchange, it works differently than, say, school grades. If one or more Experts' proposals are accepted as answers, they should usually be given an A or B grade, since they have taken the time to provide you with a working solution. If a possible solution is incomplete, ask for clarification or details before accepting the answer and grading it. People should not be given lower grades because of incorrect grammar or because you just accepted their answer or comment to close the question. The following is a good guideline to follow when grading:

A: The Expert(s) either provided you with a thorough answer or they provided you with a link to information that thoroughly answered your question. An "A" can also be given to any answer that you found informative or enlightening beyond the direct question that you asked.

B: The Expert(s) provided an acceptable solution, or a link to an acceptable solution, that you were able to use, although you may have needed a bit more information to complete the task.

C: Because Experts' reliability are often judged by their grading records, many Experts would like the opportunity to clarify if you have questions about their solutions. If you have given the Expert(s) ample time to respond to your clarification posts and you have responded to each of their posts providing requested information; or if the answers, after clarification, lack finality or do not completely address the issue presented, then a "C" grade is an option. You also have the option here of just asking Community Support to delete the question.

Remember, the Expert helping you today is probably going to be helping you next time you post a question. Give them a fair chance to earn an 'Excellent!' grade and they'll provide you with some amazing support. It's also true that a "C" is the lowest grade you can give, and the Experts know that -- so use it judiciously.

The use of a C in a vindictive manner is likely to be changed by a Moderator. You may not like the answer you get, and in some cases, and you may not like the way it is delivered, but if it is deemed to be accurate, no less than a B is an acceptable grade.





0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question