使用SubQuery进行MySQL UPDATE查询

Question

I have created a select query which shows me the correct lines that I need to update:

SELECT `subject`,`ticket_messages`.`ticket_ID` as t,
(SELECT `date` from `ticket_messages` where `ticket_ID`=t ORDER BY `date` DESC LIMIT 1) as d
 from `ticket_messages`
 LEFT JOIN `tickets` on `ticket_messages`.`ticket_ID`=`tickets`.`ticket_ID`
GROUP BY t 
 HAVING d<date_sub(curdate(), interval 5 day)
 ORDER BY t

I will be using php but working out the query first in phpmyadmin Right the above query works and gives me the correct lines. Basically it is listing anything over 5 days old. Don't worry that I am selecting subject and date, that was only so I knew I was getting the correct lines.

The question is how do I turn this into an update query? It took me a few hours to get this working already.

What I will be updating is this:

UPDATE `tickets` SET `status`=?

Basically it will be looking in the ticket_messages and finding the last message. Which is what my select query does, and then it will update in my "tickets" table the status, if the last date is over 5 days old. The tables are referentially linked.

So I need an Update with a subquery, and I have no idea to go about this.

Ok going to add a bit more. I tried this

UPDATE `tickets` SET `status`=8
WHERE
(
SELECT `subject`,`ticket_messages`.`ticket_ID` as t,
(SELECT `date` from `ticket_messages` where `ticket_ID`=t ORDER BY `date` DESC LIMIT 1) as d
 from `ticket_messages`
 LEFT JOIN `tickets` on `ticket_messages`.`ticket_ID`=`tickets`.`ticket_ID`
GROUP BY t 
 HAVING d<date_sub(curdate(), interval 5 day)
 ORDER BY t)!=null

I thought the where clause would work if it did not equal null.

Answer 1

Your first query, i don't like it because I really don't see why you use a subSelect, why you use a group by. What do you want for the date ?

Anyways you said you want only the tickets older than 5 days,

SELECT tm.ticket_ID, MAX(`date`) as d 
FROM `ticket_messages` as tm
GROUP BY tm.ticket_ID
HAVING d < date_sub(curdate(), interval 5 day)

And that's all for you first query. Tell me if you get the same ID.

Now for the update, you just have to JOIN :

 UPDATE `tickets`
 INNER JOIN 
(SELECT tm.ticket_ID, MAX(`date`) as d 
  FROM `ticket_messages` as tm
  GROUP BY tm.ticket_ID
  HAVING d < date_sub(curdate(), interval 5 day)) AS T
 ON T.ticket_ID = `tickets`.ticket_ID
 SET`status`=?