join two files based on column when there is no one-to-one correspondence in bash script (awk, grep , sed)

Question

file1.txt

112|9305|/inst.exe
112|9305|/lkj.exe
112|9305|/dje.jar
112|9305|/ind.pdf
112|9306|/ma.exe
112|9306|/ngg.pdf
112|9307|/jhhh.dat
112|9312|/ee.dat
112|9312|/qwq.dll

file2.txt

117|9305|www.gahan.com
117|9306|www.google.com
117|9312|www.mihan.com
117|9307|translate.com

I want output:

112|9305|www.gahan.com/inst.exe
112|9305|www.gahan.com/lkj.exe
112|9305|www.gahan.com/dje.jar
112|9305|www.gahan.com/ind.pdf
112|9306|www.google.com/ma.exe
112|9306|www.google.com/ngg.pdf
112|9307|translate.com/jhhh.dat
112|9312|www.mihan.com/ee.dat
112|9312|www.mihan.com/qwq.dll

I want to add the third column of file2.txt to the third column of file1.txt based on second column values. In fact, I want to join them based on the second column but there is no one-to-one correspondence between them. How can I do these with awk or grep or sed in shell script? Please help me.

Answer 1

The task can be done by one awk:

awk -F'|' 'FNR==NR{host[$2]=$3;next}{$3=host[$2] $3}1' OFS='|' file2 file1

Answer 2

Here's a possible way to do it with awk and sed working in tandem:

awk -F'|' '{print "s/"$2"|""/"$2"|"$3"/"}' file2.txt | sed -f /dev/stdin file1.txt