adding columns to tables, updating list values

[KevinO]

New user alert :)

Am presenting the challenge I'm looking to solve below. Following my research and searching, I'm at this point afraid to use the word join, union, append, or merge in this post as I'll probably mis-use it. So I'll just paste my info below. I'm seeing many posts that are allowing me to learn and 'almost' get to my desired result... but not yet there. I'll continue to research but if there's an ACE out there who has a quick solution I would take it :)

At a future date, I hope to be embarrassed at missing the simplicity of the solution I missed .....

Nuances in below:

I'm adding Table 2 column (Color) to Table A information

- if ID from Table 2 Exists in Table 1 already, then just add the new column value (ID#1 for example)

- if ID from Table 2 doesn't exist in Table 1, then add ID as new row along with new column value (ID#4 for example)

Table1
ID	Status
1	X
2	Y
3	Z

Table2
ID	Color
4	R
5	G
1	B

Desired Result
ID	Status	Color
1	X	B
2	Y
3	Z
4		R
5		G

Thanks in advance!

[LordNeilLord]

Hey @KevinO

You said the answer in your question!

• Join tool using ID to link the table
• Next a union tool, plug the L,J & R into the union :)

[tcroberts]

You'll want a join and then a Union Tool.

Essentially:

1) Join Table1 and Table2 on ID.

2) Connect all 3 output anchors to the Union Tool.

3) Configure the Union Tool to Auto Config by Name (should be the default)

Let me know if this helps,

Cheers!

[Claje · Accepted solution]

Hi,

It looks like you're trying to do a "Full Outer Join" in SQL terms, where you bring together both of these datasets and the matches.

I've attached a workflow that uses a Join and Union tool to accomplish this, and includes some notes so you can see it in process.

Hope this helps!

[KevinO]

@lordneillord @tcroberts @claje

Thanks for your quick (and correct) feedback! Much Thanks!