Getting the first or last result as part of a join
Currently I want to select the most recent value from a related table. So, I have a sale table which can have many transactions related to a single sale. I currently can use a subquery to fetch the most recent sale, as below, but it's very slow!
UPDATE Sales s
SET LastTrans = (SELECT Stamp
FROM Transactions
WHERE SalesID = s.ID
ORDER BY Desc LIMIT 1)
WHERE La开发者_运维知识库stTrans IS NULL;
Is there a way to do something like this using a join as if I do it for thousands of records it can take ages!
You didn't say what column your inner query was sorted by so I assumed it was Stamp.
UPDATE Sales s INNER JOIN (
SELECT SalesID, MAX(Stamp) AS MaxStamp FROM Transactions
GROUP BY SalesID
) AS t ON s.ID = t.SalesID
SET LastTrans = t.MaxStamp
WHERE LastTrans IS NULL;
精彩评论