CODEWITHSUNDEEP
sqlsql-serversql-server-2008
SFDCLearner
SFDCLearner

Reputation: 57

How to Compare two different tables based on multiple columns?

The first table consists of Accounts with State and Country information which are mostly correct with few wrong rows:

ID       Account       State         Country      Text
1001      Acc1          NJ           USA
1002      Acc2          NY           Null
1003      Acc3          Texas        Japan

And I have the second table which has the correct state and Country information to which the first tables needs to be compared to:

ID         State          Country
1          NJ              USA
2          NY              USA
3          Texas           USA

The query should check:

  1. If the state in the first table exists in the second table and
  2. Is it associated with the correct country.

If both the above conditions are satisfied, the first table's Text column should get updated with the state value. If any of the two conditions are not satisfied, the text column should remain blank.So the result should look like this:

ID      Account      State     Country       Text
1001     Acc1         NJ         USA          NJ
1002     Acc2         NY         USA           
1003     Acc3         Texas      USA

I am able to check whether both the conditions are satisfied, also I am able to get the rows which doesn't satisfy the condition. However, I am not sure how to go forward and update the table with the correct state. Any help would very much appreciated.

Upvotes: 1

Views: 1832

Answers (2)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can try this.

UPDATE T1
SET 
    [Text] = (CASE WHEN T1.Country = T2.Country THEN T1.[State] ELSE NULL END)
    , Country = (CASE WHEN T1.Country = T2.Country THEN T1.Country ELSE T2.Country END) 
FROM TableAccounts T1
INNER JOIN TableCountry T2 ON T1.[State] = T2.[State]

Upvotes: 3

kalaolani
kalaolani

Reputation: 322

These queries will work with MSSQL to drive an update in either direction...

SELECT State, Country FROM t1
EXCEPT
SELECT State, Country FROM t2

SELECT State, Country FROM t2
EXCEPT
SELECT State, Country FROM t1

t1 is this in your post: (ID,Account,State,Country,Text)
t2 is this in your post: (ID,State,Country)

Upvotes: 4

Related Questions