I've got a multi-table query that is updating a customer's account balance. The query involves a couple databases and has multiple aliases in it. I'm not clear what the lock statement should look like to cover the query.
I've read that you need to lock table aliases. Does this mean that I need to alias the transactions table in the INSERT to the same alias as the SELECT? What about intermediate tables like "m"?
INSERT INTO transactions(
, - s.offset_price
, NOW( )
, tx.account_balance - s.offset_price
FROM shipments AS s
INNER JOIN (
, MAX( txn_id ) AS last_tran
GROUP BY user_id
) AS m ON m.user_id = s.customer_id
AND s.shipment_id =$shipment_id
INNER JOIN transactions AS tx
ON tx.txn_id = m.last_tran
AND tx.user_id = s.customer_id