Hi
My orginal file is like (100s of lines)

id    host   ip   location  remarks
1    host1  ip1   -   xxx
2    host2  ip2   -   xxx
3    host3  ip3   -   xxx
--
--
9    host9  ip9   -   xxx

host1   location1
host2   location2
host3   location3
--
--
host9   location9

id    host   ip   location remarks
1    host1  ip1   location1   xxx
2    host2  ip2   location2   xxx
3    host3  ip3   location3   xxx
--
--
9    host9  ip9   location9   xxx

Hi,

hope this could help you..

file1:
id host ip location remarks
1 host1 ip1 - xxx
2 host2 ip2 - xxx
3 host3 ip3 - xxx
--
--
9 host9 ip9 - xxx

file2:
host1 location1
host2 location2
host3 location3
--
--
host9 location9


script:

for f in `sed '1d' file1|awk '{print $2}'`
  do
     echo $f
     value=`awk -v var=$f '{if($1~var) {print $2}}' file2`
              if [ $? -eq 0 ]
                 then
                 awk -v var=$value -v var1=$f  '{if($0~var1){print$1,$2,$3,var,$5}}' file1 |head -1 >> out.lst
                 fi
 done
 
 head -1 file1 > outnew.lst
 cat out.lst >> outnew.lst

Or:
(use nawk or /usr/xpg4/bin/awk on Solaris)

awk -F'\t' 'NR == FNR { ref[$1] = $2; next }
FNR == 1 || $4 = ref[$2] ? ref[$2] : "-"
' OFS='\t' reffile mainfile

Simply Great!

Actually it could be less verbose and the previous code won't work if you have a location named 0 or "" (null string), this one should handle those cases too:

awk -F'\t' 'NR == FNR { ref[$1] = $2; next }
($2 in ref && $4 = ref[$2]) || 1
' OFS='\t' reffile mainfile

A variation with join:

join -t' ' -11 -22 -o 2.1,2.2,2.3,1.2,2.4,2.5 reffile mainfile