Transaction table update query
Jump to navigation
Jump to search
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>
