title: MySQL UPDATE query with SubQuery
body: <p>I have created a select query which shows me the correct lines that I need to update:</p>
  
  <pre><code>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&lt;date_sub(curdate(), interval 5 day)
   ORDER BY t
  </code></pre>
  
  <p>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.</p>
  
  <p>The question is how do I turn this into an update query?
  It took me a few hours to get this working already.</p>
  
  <p>What I will be updating is this:</p>
  
  <pre><code>UPDATE `tickets` SET `status`=?
  </code></pre>
  
  <p>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.</p>
  
  <p>So I need an Update with a subquery, and I have no idea to go about this.</p>
  
  <p>Ok going to add a bit more. I tried this</p>
  
  <pre><code>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&lt;date_sub(curdate(), interval 5 day)
   ORDER BY t)!=null
  </code></pre>
  
  <p>I thought the where clause would work if it did not equal null.</p>