CODEWITHSUNDEEP
sqlms-accessjoin
Nina
Nina

Reputation: 151

SQL update joining 2 tables

In MS Access I am trying to update 1 table based on a concatenation of columns NOT matching a concatenation of columns in another table. Here is what I need to do.

If ((Table1.A + Table1.B + Table1.C + Table1.D) 
    NOT EQUAL TO (Table2.E + Table2.F + Table2.G + Table2.H))
Then set Table1.error = x

I know this is some type of SQL join but I'm struggling with it.

Update: Ok, I finally got something to work. This is what I have:

SELECT [Table1].[A] & [Table1].[B] & [Table1].[C] & [Table1].[D] AS Expr1
FROM Table1, Table2
WHERE ((([Table2].[E] & [Table2].[F] & [Table2].[G] & [Table2].[H])<>
[Table1].[A] & [Table1].[B] & [Table1].[C] & [Table1].[D]));

That'll run but it shows me every unmatched line. I need to search table 1 for any rows that don't exist in table 2 and only return those unmatched rows in table 1.

Upvotes: 0

Views: 127

Answers (5)

Nina
Nina

Reputation: 151

I realized that the fields didn't need to be concatenated (they were concatenated in previous code so I assumed it was for a reason). So I did an unmatched query wizard with the 4 fields separately within the same query.

Upvotes: 0

abraxascarab
abraxascarab

Reputation: 701

Assuming field [E] in Table 2 can never be null, you can find your UNMATCHED Table 1 records by searching for Nulls after a Left Join:

SELECT [T1].[A] & [T1].[B] & [T1].[C] & [T1].[D] AS Concat1
FROM 
   [Table1] as T1
   Left Join [Table2] as T2 ON
      ((([T2].[E] & [T2].[F] & [T2].[G] & [T2].[H]) = [T1].[A] & [T1].[B] & [T1].[C] & [T1].[D]))
WHERE
   [T2].[E] is NULL;

This will return All your Table 1 records that don't have a matching Table 2 record.

You can call that query into your update.

Upvotes: 0

M_Idrees
M_Idrees

Reputation: 2172

UPDATE Table1 AS T1
INNER JOIN Table2 AS T2 
    ON (Table1.A + Table1.B + Table1.C + Table1.D)  NOT EQUAL TO  (Table2.E + Table2.F + Table2.G + Table2.H))
set T1.error = x

It will update Table1.error with inner join Table2 on condition of columns' sum not equal.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

You could try an update join with on clause for the <> results

      update T
      set t.error = x
      FROM Table1 AS T
      INNER JOIN ( 
          SELECT Table2.E + Table2.F + Table2.G + Table2.H as  T2_RESULT
          from  Table2 
      )  AS ON T2.T2_RESULT <> (Table1.A + Table1.B + Table1.C + Table1.D)

Upvotes: 1

Andrey Khmelev
Andrey Khmelev

Reputation: 1161

UPDATE (SELECT Table1.error               
          FROM Table1,
               Table2
         WHERE ((Table1.A + Table1.B + Table1.C + Table1.D) 
               NOT EQUAL TO (Table2.E + Table2.F + Table2.G + Table2.H)))
   SET Table1.error = x

Upvotes: 1

Related Questions