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:

Finding the average of a column excluding certain rows using AWK

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

Extracting Substring from File Name

Comparing calendar objects

awk ternay operator, count fs with ,

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

How to remove characters before and including an underscore?

Double compare in JavaScript looks weird [duplicate]

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

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

Python - Using a created list as a parameter

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

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

TSQL update value with subquery

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

find numbers divisible by 3 in csv file using shell script

Suppressing system command called from awk script

Bash modify CSV to change a field

AWK|BASH, use double FS and ternary operator

Change a Script to a For Do Done Loop

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

Access a bash array in awk loop

Compare 2 sheets with different headers

Compare strings without being case-sensitive

Efficient comparison of small integer vectors

Extracting columns within a range AWK

Order LINQ query elements by a collection of numbers

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

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

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

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

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

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

Selecting unique lines based on two columns

BASH - conditional sum of columns and rows in csv file

Awk Script to process data from a trace file

loop add a comma after nth comma using awk

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

Convert AWK command to sqlite query

Using AWK to parse fields with commas

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

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

Repeating the format specifiers in awk

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

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

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

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

WPF Listbox Collection custom sort

Synchronize local SQLite database with central database

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