Tracking Number Generation Process

$trackingPrefix = "PI" . date("dmY");
$processTypeId = 1;
$updatePurchaseTrackingNo = DB::statement("update  ins_purchase_invoice, ins_purchase_invoice as table2
SET ins_purchase_invoice.tracking_no=(
   select concat('$trackingPrefix',
    LPAD( IFNULL(MAX(SUBSTR(table2.tracking_no,-4,4) )+1,0),4,'0')
                  ) as tracking_no
   from (select * from ins_purchase_invoice ) as table2
   where table2.id !=  $purchaseData->id
and table2.tracking_no like '$trackingPrefix%'
   )
where ins_purchase_invoice.id = $purchaseData->id and table2.id = $purchaseData->id");