Compare two files and append the values, leave the mismatches as such in the output file

Question:

I'm trying to match two files,file1.txt(50,000 lines), file2.txt(55,000 lines). I want to campare file2 to file 1 extract the values of column 2 and 3 and leave the mismatches as such. Output file must contain all the ids from file2 i.e., it should have 55000 lines. Note: All the ids in file 1 are not present in file2. i.e the actual matches could be less than 50,000.

file1.txt

ab1 12 345  
ab2 9 456  
gh67 6 987  

file2.txt

ab2 0 0  
ab1 0 345  
nh7 0 0  
gh67 6 987  

Output

ab2 9 456  
ab1 12 345  
nh7 0 0  
gh67 6 987 

This is what i tried but it only print the matches (so instead of 55,000 lines i have 49,000 lines in my output file)

awk "NR==FNR {f[$1]=$0;next}$1 in f{print f[$1],$0}" file1.txt file2.txt >output.txt

This awk script will work

NR == FNR {
    a[$1] = $0
    next
}
$1 in a {
    split(a[$1], b)
    print $1, (b[2] == $2 ? $2 : b[2]), (b[3] == $3 ? $3 : b[3])
}
!($1 in a)

If you save this as a.awk and run

awk -f a.awk foo.txt foo1.txt

This will output

ab2 9 456
ab1 12 345
nh7 0 0
gh67 6 987

Related:

Compare 2 sheets with different headers

awk ternay operator, count fs with ,

How to split a CSV file into multiple files based on column value

Compare 2 csv files and output different rows to a 3rd CSV file using Python 2.7

Finding columns with only white space in a text file and replace them with a unique separator

Using blank-line delimited records and colon-separated fields in awk

Extracting columns within a range AWK

Check for decimal point and add it at the end if its not there using awk/perl

Bash, Using grep, sed or awk to extract section of text and then match

How to append entry the end of a multi-line entry using any of stream editors like sed or awk

WPF Listbox Collection custom sort

Compare 2 seperate csv files and write difference to a new csv file - Python 2.7

Taking multiple header (rows matching condition) and convert into a column

Access a bash array in awk loop

Using a command-line utility to perform the following map-updates

find numbers divisible by 3 in csv file using shell script

Selecting unique lines based on two columns

AWK|BASH, use double FS and ternary operator

BASH - conditional sum of columns and rows in csv file

Finding the average of a column excluding certain rows using AWK

Python - Using a created list as a parameter

Extracting Substring from File Name

Repeating the format specifiers in awk

How to remove characters before and including an underscore?

Remove part of a column, if its in a specific Column number. (The column has a variable)

Comparing calendar objects

Split a column's values into multiple rows awk/unix/python

Double compare in JavaScript looks weird [duplicate]

Awk Script to process data from a trace file

AngularJS Filter two select inputs with values less than or equal to value

Suppressing system command called from awk script

Using AWK to parse fields with commas

Order LINQ query elements by a collection of numbers

Ignore first few lines and last few lines in a file Linux

if a string exist (including a variable), flip it using awk or sed

AWK count number of times a term appear with respect to other columns

Change a Script to a For Do Done Loop

how to deletes line from a text file that are taken from another file [duplicate]

Synchronize local SQLite database with central database

Convert AWK command to sqlite query

Efficient comparison of small integer vectors

Compare strings without being case-sensitive

Print the last 1,2,3..Nth or first 1,2,3…Nth matching block pattern using awk or sed

AWK write to new column base on if else of other column

How to match and change strings in a column of a semicolon separated file?

Bash modify CSV to change a field

Replace [a-z],[a-z] with [a-z], [a-z] and keep the letters

How to find average and maximum in an interval using Shell [closed]

loop add a comma after nth comma using awk

TSQL update value with subquery