How to replace a specific word in specific column?

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

I have a ref file like

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

Now I want the first file like

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

Note: The delimiter is "tab". The word is not the last word in any of the line...
Can you suggest me an idea?

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

output:
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

Thanks
Sha

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

Use appropriate separator (-t option)

As I was just palying around with the join command I discovered some neat feature like performing a "left join" type of join:

join -t' ' -a1 -e'unknown' -12 -21 -o 1.1,1.2,1.3,2.2,1.4,1.5 mainfile reffile

This will return:

1 host1 ip1 location1 - xxx
2 host2 ip2 location2 - xxx
3 host33 ip3 unknown - xxx
9 host9 ip9 location9 - xxx

Of course, files need to be sorted first which is not the case with awk.