[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

Update query with multiple joins

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
edrz01
Asked:
edrz01
  • 5
1 Solution
 
shambaladCommented:
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
 
shambaladCommented:
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
 
shambaladCommented:
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
shambaladCommented:
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
 
shambaladCommented:
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
 
edrz01Author Commented:
Thanks!
0

Featured Post

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!

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now