Casey Crookston Casey Crookston - 3 months ago 9
SQL Question

SQL Query to Find Mis-Matching Data

I have a local User table which gets it's data from our client. Once a week, they will supply us with an updated version of their user data. We then need to feed any new users into our User table (that's easy), plus update any existing users with any new data (this is where I'm stuck).

So, let's say our local table is called UserLocal, and then we have a table that gets wiped out and re-written once a week with the new data from the client called UserNew. The columns, with a sample of one user, looks like this:

UserLocal

UserId UserCode FirstName LastName
12345 ABC John Doe


But, the new data feed from the client looks like this:

UserNew

UserId UserCode FirstName LastName
12345 XYZ John Doe


So, the UserCode for John has changed. What I'm trying to do is write a query that finds all rows in UserNew where the UserId matches a row in UserLocal, but the UserCode does not match.

Having a hard time with that syntax.

Answer

The basic idea would be:

SELECT *
FROM UserNew n
    JOIN UserLocal l
        ON l.UserId = n.UserId
        AND l.UserCode != n.UserCode