Update specific data points when joining two different data streams

[rev]

Hello Everyone,

I have a file "reference" that only has unique data and takes data from another file "input".

But "reference" file should always contain up to date information.

And I only need to update, for example, ID-2090's rate and bill while leaving the data in other columns untouched.

While "reference" file might contain only 7 columns, "input" file contains 30/40 columns. 
So I need to update the Pace and Measure for a person when:

1. They are available in both files.

2. They are only available in "reference" file - then keep all the data as is.

3. They are only available in "input" file, then add their data to the "reference" file.

Unique ID	Name	Pace	Measure	Column A	Column B	Column C
1012	ABC	200	650	Data	Data	Data
2012	DEF	300	750	Data	Data	Data
2090	EFG	400	850	Data	Data	Data
2098	XYZ	500	950	Data	Data	Data
2080	BNC	600	1500	Data	Data	Data
1080	NBC	350	800	Data	Data	Data

The problem I am facing is that I am not able to update only the Pace and Measure of an ID available in both files while keeping the rest of the data constant. Primary key here would be the ID which is unique.

Any help is much appreciated. Thanks!

[gc]

Can you split Reference into two parts using Select tool, where part1 is unique ID+Pace+Measure and part 2 is uniqueID+<all other columns not Pace and Not measure>. Then do your processing on Part 1.  Then join Part 1 and 2 on uniqueID. Something like that?

[jdunkerley79 · Accepted solution]

I think a Join tool on Unique ID followed by a Union tool should do what you want.

Attached a quick go

[rev]

There was my mistake! I was union-ing the data at the first go instead of joining it and then choosing which stream data point to include. Thanks for your solution!!