Awk/Grep - Pull Value from row and replace if it matches File data [Find->Compare->Replace]

Question

I have a file with long rows and a file with reference data. Flong, Fref.

My goal is to search specific field(s) from Flong rows compare them to all values from Fref and replace them with the given value if they match.

Basically search file Flong - row 1 - field 4 against Fref - all rows - field 1.
If it matches check field 6 against field 2, if that matches replace field 4 with field 3. If 1 or 2 don't match then ignore it, make no changes.

Flong contains data like:

Name|location|111|22|333|4444| |6666||8
Name|location| |56|67|| |6666||8

Some fields are empty and some have spaces but all are separated by '|'

Fref contains data like:

574|5327|1000
22|4444|2000
67|77|3000

RETURN:

Name|location|111|2000|333|4444| |6666||8
Name|location| |56|67|| |6666||8

I know this is do-able with a function but I have been shocked by the complexity people can work into Awk so I have been trying to use that but any string search/editor is usable.

Answer 1

Based on your stated RETURN, what you appear to want to do is construct a hash (associative array) keyed on fields 1 and 2 of Fref, and then use fields 4 and 6 of Flong as a lookup key:

$ awk -F'|' '
    BEGIN{OFS = FS} 
    NR == FNR {a[$1 FS $2] = $3; next} 
    $4 FS $6 in a {$4 = a[$4 FS $6]} 
    1
' Fref Flong
Name|location|111|2000|333|4444| |6666||8
Name|location| |56|67|| |6666||8