new data needs to be added and unmatched data needs kept/changed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Every quarter I recieve an updated list of users. What I need to be able to
is match this new list up with a master user list and;
A) keep what matches,
B) Add new records to master if not in,
c) change a field in master to 'inactive' if not on new list.
Both Tables have the same structure:
ID,First, Last, Loc, Role, ACDGroup, loginName, Status

I know there's probably a simple solution that I'm overlooking, but if
anyone could assist I'd appreciate it.
 
First question - is ID the primary key (always unique and matches from quarter
to quarter)? Assuming that is the case, then you could do something like the
following UNTESTED queries. Try this on a COPY of your data to see if it works.

A) Make Changes to existing records if data has changed.

UPDATE Master AS M LEFT JOIN Quarter as Q
ON M.ID = Q.ID
SET M.First = Q.First,
M.Last = Q.Last,
M.LOC = Q.Loc,
M.Role = Q.Role,
M.ACDGroup = Q.ACDGroup,
M.LoginName = Q.LoginName,
M.Status = Q.Status
WHERE M.First <> Q.First OR
M.Last <> Q.Last OR
M.LOC <> Q.Loc OR
M.Role <> Q.Role OR
M.ACDGroup <> Q.ACDGroup OR
M.LoginName <> Q.LoginName OR
M.Status <> Q.Status


B) Add new records

INSERT INTO MASTER (ID,First, Last, Loc, Role,
ACDGroup, loginName, Status)
SELECT Q.ID, Q.First, Q.Last, Q.Loc, Q.Role,
Q.ACDGroup, Q.loginName, Q.Status
FROM Quarter as Q LEFT JOIN MASTER ON
Q.ID = Master.ID
WHERE Master.Id Is Null

C) Set to inactive

UPDATE Master AS M LEFT JOIN Quarter as Q
ON M.ID = Q.ID
SET ActiveState = "Inactive"
WHERE Q.ID Is Null
 
You assume correctly, the ID is unique. I'm not at the same site as my test
db's but I'll try this tomorrow and let you know how/if it works. Looks like
it will, I can kind of "see" where that query is going... (so to speak)

Thanks for the reply.
 
Back
Top