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:

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

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

Compare strings without being case-sensitive

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

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

Change a Script to a For Do Done Loop

Extracting Substring from File Name

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

Repeating the format specifiers in awk

Finding the average of a column excluding certain rows using AWK

Awk Script to process data from a trace file

loop add a comma after nth comma using awk

Suppressing system command called from awk script

Comparing calendar objects

Extracting columns within a range AWK

Double compare in JavaScript looks weird [duplicate]

find numbers divisible by 3 in csv file using shell script

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

Convert AWK command to sqlite query

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

Access a bash array in awk loop

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

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

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

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

Synchronize local SQLite database with central database

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

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

awk ternay operator, count fs with ,

Using AWK to parse fields with commas

Selecting unique lines based on two columns

Bash modify CSV to change a field

BASH - conditional sum of columns and rows in csv file

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

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

AWK|BASH, use double FS and ternary operator

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

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

Python - Using a created list as a parameter

TSQL update value with subquery

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

WPF Listbox Collection custom sort

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

Order LINQ query elements by a collection of numbers

Compare 2 sheets with different headers

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

Efficient comparison of small integer vectors

How to remove characters before and including an underscore?

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