?
Solved

Update query with multiple joins

Posted on 2009-05-06
6
Medium Priority
?
195 Views
Last Modified: 2012-05-06
I have 2 tables from which I am comparing several fields. I want to update a non-compared field (checkbox) in the first table if any of the conditions are met.

I can do the join's and then union them together but can't figure out how to get the update part to work.

The example would be:
Table1 - Name, Address, Phone
Table2 - Name, Address, Phone

I want to only show results if
Table1.Name<>Table2.name joined on Table1.Address=Table2.Address
OR
Table1.Address<>Table2.Address joined on Table1.Name=Table2.name
OR
Table1.Phone<>Table2.Phone joined on Table1.Name=Table2.name

If any of these records show up then it would place a check in the box (or yes or true)

Thanks!

0
Comment
Question by:edrz01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
6 Comments
 
LVL 7

Expert Comment

by:shambalad
ID: 24317823
You need to set the query type to 'Dynaset (Inconsistent updates)'; see image below.
You can open the Query Properties dialog by right-clicking your mouse in an open area of the 'Tables' window while in Query Design.

Query-Properties.jpg
0
 
LVL 7

Expert Comment

by:shambalad
ID: 24317856
By the way, you can't do an update (inconsistent or otherwise) on a Union query.
You'd probably have better luck doing this as 3 separate queries.
Todd
0
 
LVL 7

Expert Comment

by:shambalad
ID: 24317950
I'll admit I haven't been much help to this point. I'm putting together the actual queries for you, but I have to step out for about 45 minutes.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 7

Expert Comment

by:shambalad
ID: 24318736
Try this:

UPDATE Table1 AS A LEFT JOIN Table2 AS B ON A.Name = B.Name SET A.MisMatchError = True WHERE A.Address<>B.Address OR A.Phone<>B.Phone OR B.Address Is Null

For the above query, I added a field 'MisMatchError' to table1.

Table1-Design.jpg
0
 
LVL 7

Accepted Solution

by:
shambalad earned 2000 total points
ID: 24319019
The previous query update the MisMatch byte to true under the following circumstances:
1. Table1 Name = Table2 Name AND table1 Address <> table2 Address
    "A.Address<>B.Address"
2. Table1 Name = Table2 Name AND table1 Phone <> table2 Phone
   "A.Phone<>B.Phone"
3. Table1 Name does not match any of the Names in Table 2
   "B.Address Is Null"

There is a potential weakness in the 3rd criterion, since it would be true if the Names match and the Address in Table 2 is null, so to more correctly satisfy the 3rd condition then query should be:

UPDATE Table1 AS A LEFT JOIN Table2 AS B ON A.Name = B.Name SET A.MisMatchError = True WHERE A.Address<>B.Address OR A.Phone<>B.Phone OR B.Name Is Null

If, per chance, both Names are Null, they still wouldn't be equal since two Nulls will never evaluate as True.

Beyond that, though, the 3rd criterion is not really what you originally asked for; and could be problematic. For instance, if  not having a matching Name in Table2 is not an error. Then the above query will be incorrectly taging records in table1 that don't have a matching record in Table2.

To match your specs (no more, no less), you really would need two queries:

UPDATE Table1 AS A LEFT JOIN Table2 AS B ON A.Name = B.Name SET A.MisMatchError = True WHERE A.Address<>B.Address OR A.Phone<>B.Phone

UPDATE Table1 AS A INNER JOIN Table2 AS B ON A.Address = B.Address SET A.MisMatchError = True WHERE A.Name<>B.Name
0
 

Author Closing Comment

by:edrz01
ID: 31578585
Thanks!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

752 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