Update Query

[musclecarlover07]

I need some help with an update query. So I have 2 tables. One table with the live data and then the second is the newly imported data. I want to hit a button then the data is matched within the 2 tables. IF any of the information is different then the old table with live data is changed to reflect the new data.

So if I have table1 and table2. both tables have the same fields First Name last name DOB and contact number. Example:

Live Data
First Name Last Name DOB Contact Number
John Doe 8/17/1987 012-345-6789

But New data is
First Name Last Name DOB Contact Number
John Doe 8/17/1987 987-654-3210

I want to hit a button then it goes through and updates the info. I hope this makes some sort of sense. :banghead:

 

[JHB]

Does the name also change? If yes, how do you then determine then correct person?
Does the "New data" contains persons there not are in the "Live data"?

 

[musclecarlover07]

No the name stays the same. Yes there is persons in the New data that is not in the live data.

 

[JANR]

You want to add new persons as well as updating old records?

Considery an update query where you do an outer join (left or right) on firstname and lastname and DOB. (see attached image for a visual example)

Query SQL:

UPDATE NewData LEFT JOIN LiveData ON (NewData.FirstName = LiveData.FirstName)
 AND (NewData.LastName = LiveData.LastName)
 AND (NewData.DOB = LiveData.DOB)
 SET LiveData.FirstName = [NewData].[FirstName], LiveData.LastName = [NewData].[LastName], LiveData.DOB = [NewData].[DOB], LiveData.ContactNumber = [NewData].[ContactNumber];

This will both add new records and update old ones.

If you only need to update records and don't want to add new ones, change the JOIN to Inner Join.

UPDATE NewData INNER JOIN LiveData ON (NewData.DOB = LiveData.DOB)
 AND (NewData.LastName = LiveData.LastName)
 AND (NewData.FirstName = LiveData.FirstName)
 SET LiveData.ContactNumber = [NewData].[ContactNumber];

But are you sure there will be no duplicates, no second John Doe who also has the same birthday as the first John Doe you have in live data. I would think that it is possible that in the future there could be one.

I would perhaps find some other way to uniquely identify a record.

Good luck

Janr