Solved

Update query with multiple joins

Posted on 2009-05-06
6
193 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

688 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