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 seperate csv files and write difference to a new csv file - Python 2.7

awk ternay operator, count fs with ,

Extracting Substring from File Name

Synchronize local SQLite database with central database

Compare strings without being case-sensitive

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

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

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

Change a Script to a For Do Done Loop

Double compare in JavaScript looks weird [duplicate]

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

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

Comparing calendar objects

find numbers divisible by 3 in csv file using shell script

Finding the average of a column excluding certain rows using AWK

Selecting unique lines based on two columns

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

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

WPF Listbox Collection custom sort

Python - Using a created list as a parameter

Awk Script to process data from a trace file

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

Order LINQ query elements by a collection of numbers

Repeating the format specifiers in awk

Bash modify CSV to change a field

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

BASH - conditional sum of columns and rows in csv file

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

Convert AWK command to sqlite query

AWK|BASH, use double FS and ternary operator

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

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

How to remove characters before and including an underscore?

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

Suppressing system command called from awk script

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

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

TSQL update value with subquery

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

Compare 2 sheets with different headers

Access a bash array in awk loop

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

Efficient comparison of small integer vectors

Using AWK to parse fields with commas

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

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

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

Extracting columns within a range AWK

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

loop add a comma after nth comma using awk