Complex awk replace from one file to another

Question

I need to replace part of values in one file with values from another if one field matches.

File to be replaced:

NULL,NULL,1,'2017-01-01 00:00:00','2017-12-31 23:59:59','SE','AL',300,200,12,1,24,1,19,110,220,1
NULL,NULL,1,'2017-01-01 00:00:00','2017-12-31 23:59:59','SE','AG',300,200,12,1,24,1,19,110,220,1

File that contains the correct values:

'AL',220,110
'DZ',379,189.5
'AO',931,465.5
'AG',659,329.5

So in file1 for 'AL' match I should replace column8 with column2 from file2 and column9 with column3 from file2. How to achieve this with awk or something similar, but not perl, thanks. :)

And the conditions in some sort of pseudo code:

if (file2[col1] == file1[col7]) 
  file1[col8] = file2[col2]
  file1[col9] = file2[col3]

Answer 1

The below awk should work just fine for you

awk -F"," 'BEGIN{OFS=",";} FNR==NR{col2[$1]=$2; col3[$1]=$3;next} ($7 in col2){$8=col2[$7]; $9=col3[$7];print}' file2 file1
NULL,NULL,1,'2017-01-01 00:00:00','2017-12-31 23:59:59','SE','AL',220,110,12,1,24,1,19,110,220,1
NULL,NULL,1,'2017-01-01 00:00:00','2017-12-31 23:59:59','SE','AG',659,329.5,12,1,24,1,19,110,220,1

The idea is to first create a hash array from file2 with the actual column 2 and column 3 values per column 1 and maintain it in arrays col2 and col3. Then on parsing file1 with the condition ($7 in col2 && $7 in col3) we select those lines in file1 whose $7 belongs in the either of the arrays and if so, substitute the values.