Awk Compare File1 File2 on f2

Shell Programming and Scripting
1

I'm trying to compare two files using AWK, where if field2 of both files match, replace field1 of file1 with field1 of file2 and if there is no match just print the line of file1.

file1.txt (has empty first field)

:ABBATOM:B:H:1992
:ABBA TROJAN:B:H:1993
:ABBES FIRST HOPE:B:M:1997
:ABBEYS LENDING:BR:M:1996
:ABBEYS LEGACY:BR:M:1997
:ABBIE VELMA:B:M:1992
:ABBIESRUN:B:M:1995

file2.txt

31:ABBATOM:B:H:1992
32:ABBA TROJAN:B:H:1993
35:ABBIE VELMA:B:M:1992
36:ABBIESRUN:B:M:1995

DESIRED OUTPUT

31:ABBATOM:B:H:1992
32:ABBA TROJAN:B:H:1993
:ABBES FIRST HOPE:B:M:97
:ABBEYS LENDING:BR:M:1996
:ABBEYS LEGACY:BR:M:1997
35:ABBIE VELMA:B:M:1992
36:ABBIESRUN:B:M:1995

Any help would be appreciated.

2

You should try to solve your problem before asking for help:

awk -F':' 'NR==FNR{_[$2]=$1;next}$2 in _{$1=_[$2]}1' OFS=':' f2 f1
3

Thanks, i did try to solve it and did many searches here using keywords awk and compare. However, i kept screwing it up, am very confused at this point, and got nowhere and i really don't want to mess-up my 743,000 record database file, with something like:

FNR==NR { arr[$2]=$1; next}
$2 in arr { print arr[$1], $0 }

if (field1 of file2) == (field2 of file1) then
awk -F: 'FNR == NR {
 { f1[$2]=$1 $2
  next;
  }
if ($2 in f1) print  f1[$1]$0; else print $0;}' ~/Desktop/file2.txt ~/Desktop/file1.txt > ~/Desktop/outputfile.txt
4

Always backup your working file.

This will print only if $2 is in arr.

5

Yes, words to live by, and backups have saved my rear-end many a time.

Sorry was thinking out loud and that line was actually commented out in program.

Told you i was totally confused. :smiley:

Your solution worked and is much more elegant than anything i was working on:

awk -F':' 'NR==FNR{_[$2]=$1;next}$2 in _{$1=_[$2]}1' OFS=':' f2 f1

Thanks.