Join two datasets (main search and subsearch), keep all keys in both, and update non-key fields with data from the subsearch

[cdhippen]

If I have two searches, one generates fields "key A" and "Column A" and the second search generates fields "key B" "Column B" and I want to join them together, keep all keys in "key A" and update the values that exist in key A AND key B with the values in Column B, leaving column A values as a fallback for keys that don't appear in column B, how would I do that?
|key| data| |key|data |
|---|---|---|---|---|
| A | 123 | | A | 456 |
| B | 123 | | B | 456 |
| C | 123 | | C | NULL |
| D | 123 | | G | 456 |
| E | 123 | | H | 456 |
| F | 123 | | I | 123 |
||
V
|key | data|
|---|---|
| A | 456 |
| B | 456 |
| C | 123 |
| D | 123 |
| E | 123 |
| F | 123 |
| G | 456 |
| H | 456 |
| I | 123 |

I tried main search | join type=outer max=0 [| subsearch] but it doesn't appear to be working because when I tried
main search NOT key=A [| subsearch] I would expect A to be removed from the A search but replaced with B search, but that didn't happen, so I think I'm losing values from the B search.

[DMohn · Accepted solution]

Assuming both searches have the field "key" and "data" you could go like this...

<your_main_search> | rename data as data1 | join type=outer max=0 key [<your_second_search> | rename data as data2] | eval data=coalesce(data2, data1) | table key data

[cdhippen]

This isn't working for me. If I limit the data of the main search (for testing) by saying | inputlookup x-x WHERE key=A and the subsearch results in key=A, key=B, key=C etc, the end result still only returns key=A. I need a way to keep all the results from both searches.

[DMohn]

Ah, right ... You can't join a key where there is none on the left side of the join. Try it that way:

| inputlookup first_lookup | remane data as data1 | append [| inputlookup second_lookup | rename data as data2] | stats values(data1) as data1, values(data2) as data2 by key | eval data=coalesce(data2,data1) | table key data

[cdhippen]

I forgot to reply that I figured it out, but your solution here is more or less what I did in my search. Thanks for the ideas! The coalesce idea was very valuable.