If one string matches at the beginning of last line in a specific file then replace the other string from same line.using regex groups?

sed might be easier here

$ sed -E '$s/(^235791@.*)SUCCESS$/\1FAILURE/' file

you can add -i for in place update.

To pass id as a variable

$ id=235791; sed -E '$s/(^'$id'@.*)SUCCESS$/\1FAILURE/' file

you should double quote "$id" ideally, but if you're sure about the contents you may get away without.

Could you please try following, written and tested with shown samples in GNU awk. You need not to use many commands for this one, we could do this in a single awk itself.

One liner form of code:

awk -v id="$your_shell_variable" 'BEGIN{ FS=OFS="@@@" } NR>1{print prev} {prev=$0} END{if($1==id && $NF=="SUCCESS"){$NF="FAILURE"}; print}' Input_file > temp && mv temp Input_file

Explanation: Adding detailed explanation for above.

awk -v id="$your_shell_variable"'                                     ##Starting awk program from here.
NR>1{                                     ##Checking condition if prev is NOT NULL then do following.
  print prev                              ##Printing prev here.
}
{
  prev=$0                                 ##Assigning current line to prev here.
}
END{                                      ##Starting END block of this program from here.
  if($1==id && $NF=="SUCCESS"){       ##Checking condition if first field is 235791 and last field is SUCCESS then do following.
    $NF="FAILURE"                         ##Setting last field FAILURE here.
  }
  print                                   ##Printing last line here.
}
'  Input_file > temp && mv temp Input_file                           ##Mentioning Input_file name here.

2nd solution: As per Ed sir's comment some awk's don't support $1, $NF in END sections so if above doesn't work for someone please try more generic solution as follows.

One liner form of solution(since specifically asking it):

awk -v id="$your_shell_variable" 'BEGIN{ FS=OFS="@@@" } NR>1{print prev} {prev=$0} END{num=split(prev,array,"@@@");if(array[1]==id && array[num]=="SUCCESS"){array[num]="FAILURE"};for(i=1;i<=num;i++){val=(val?val OFS:"")array[i]};print val}' Input_file > temp && mv temp Input_file

Detailed level(non-one liner code):

awk -v id="$your_shell_variable" '
BEGIN{ FS=OFS="@@@" }
NR>1{
  print prev
}
{
  prev=$0
}
END{
  num=split(prev,array,"@@@")
  if(array[1]==id && array[num]=="SUCCESS"){
    array[num]="FAILURE"
  }
  for(i=1;i<=num;i++){
    val=(val?val OFS:"")array[i]
  }
  print val
}
'  Input_file  > temp && mv temp Input_file

With GNU sed

sed -E '${/^235791\>/ s/SUCCESS$/FAILURE/}' file

Or with the BSD sed on MacOS

sed -E '${/^235791@/ s/SUCCESS$/FAILURE/;}' file

When working with "the last X in the file", it's often easier to reverse the file and work with "the first X":

tac file | awk '
    BEGIN {FS = OFS = "@@@"} 
    NR == 1 && $1 == 235791 && $NF == "SUCCESS" {$NF = "FAILURE"}
    1
' | tac

$ awk -v val='235791' '
    BEGIN { FS=OFS="@@@" }
    NR>1 { print prev }
    { prev=$0 }
    END {
        $0=prev
        if ($1 == val) {
            $NF="FAILURE"
        }
        print
    }
' file
235788@@@235788@@@20200724_103122@@@SUCCESS
235791@@@235791@@@20200724_105934@@@SUCCESS
235833@@@235833@@@20200724_130652@@@FAILURE
235842@@@235842@@@20200724_132721@@@FAILURE
235852@@@235852@@@20200724_134607@@@FAILURE
235791@@@235791@@@20200724_105934@@@FAILURE