CODEWITHSUNDEEP
mysqlsqldatabase
phil-lavin
phil-lavin

Reputation: 1227

MySQL UPDATE with a JOIN to a subselect

I have a table called sagepaydirect__transactions which I am updating with a migration to set its response field to the response field of the table called sagepaydirect__responses.

The two tenuously link via a table named sagepaydirect__requests on the last 4 digits of the _ separated string in the field vendor_tx_code.

The problem is that sagepaydirect__requests will have multiple rows when joined to sagepaydirect__transactions. What I want to do is do the JOIN such that the UPDATE takes the value from the row in sagepaydirect__transactions whose ID is numerically higher.

This is my attempt at the query but the complaint is that t.id does not exist.

UPDATE sagepaydirect__transactions t
INNER JOIN (
        SELECT * FROM sagepaydirect__requests rq
        WHERE SUBSTRING_INDEX(rq.vendor_tx_code, '_', -1) = t.id
        ORDER BY id DESC
        LIMIT 1
) rq_table
ON t.id = SUBSTRING_INDEX(rq_table.vendor_tx_code, '_', -1)
INNER JOIN sagepaydirect__responses rs
ON rq.id = rs.request_id
SET t.response = rs.status_detail

Upvotes: 1

Views: 3382

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You are trying to use a correlated subquery in the from clause. That won't work. The following uses aggregation to get the maximum id, and then continues with the joins.

UPDATE sagepaydirect__transactions t INNER JOIN
       (SELECT SUBSTRING_INDEX(rq.vendor_tx_code, '_', -1) as val, max(id) as maxid
        FROM sagepaydirect__requests rq
        WHERE SUBSTRING_INDEX(rq.vendor_tx_code, '_', -1) = t.id
        group by id SUBSTRING_INDEX(rq.vendor_tx_code, '_', -1)
      ) rq
      ON t.id = rq.val INNER JOIN
      sagepaydirect__responses rs
      ON rq.id = rs.request_id
    SET t.response = rs.status_detail;

Upvotes: 3

Related Questions