Transaction table update query

Query to update the transaction ids in payment table to populate the fields which became null due to a bug in payment reception page.

<source lang="sql" highlight="3"> UPDATE st_payment SET payment_tran_id = transactioninfo.transactionid FROM (

SELECT * FROM (

SELECT payment_id,transactionid FROM st_payment

LEFT JOIN (

SELECT pa_sb_id , MAX(pa_id ) AS transactionid FROM st_payment_attempts WHERE pa_sb_id IS NOT NULL GROUP BY pa_sb_id

) AS transdet ON transdet.pa_sb_id=st_payment.payment_sb_id

WHERE payment_tran_id IS NULL AND payment_sb_id IS NOT NULL AND payment_status = 2

) AS transactioninfodet ) AS transactioninfo WHERE transactioninfo.payment_id=st_payment.payment_id

</source>