MySQL UPDATE query with SubQuery

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.

I have looked at that page and don't even know what it is talking about. Not even sure on what an MCVE is let alone make one. — Thomas Williams
– Thomas Williams, Commented Oct 3, 2016 at 14:12
So, add a WHERE clause to your UPDATE with basically what you're using to check if it's over 5 days, n'est-ce pas? — Funk Forty Niner
– Funk Forty Niner, Commented Oct 3, 2016 at 14:14

Daniel E. · Accepted Answer · 2016-10-03 14:38:38Z

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`=?

edited Oct 3, 2016 at 14:38

answered Oct 3, 2016 at 14:28

Daniel E.

2,4811 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thomas Williams Over a year ago

I will explain. The need for the ticket subquery is this. In ticket_messages there will be multiple ids, as you can have multiple tickets per id. The subquery gives me the last ticket date for an id. So the subquery is important. I can't just use date as I need the last date. The group by is important as otherwise I would have multiple lines of the ticket. I only want the last line of every ticket.

Thomas Williams Over a year ago

Your first query works and gives me 4 results which is correct. The second query gives me 12 results which is incorrect. I am wondering if an inner join is the same as a left join. We are definitely closer though

Thomas Williams Over a year ago

Right I think it is working now. Strangely when I first run the query it over-wrote all 12 entries. but subsequent runs are now only doing 4. So I think it is working now. Perhaps I did something silly. Will do a little more work on it and get back to you. Your solution however looks a lot simpler than I came up with though

Thomas Williams Over a year ago

Ok I am going to give this a tick now. The first time I run the code I must have done something silly because subsequent runs are now working fine. Strangely enough I originally used MAX date, and it was giving me incorrect dates back, but then I was originally using a where clause instead of a having clause. Thanks a lot

Collectives™ on Stack Overflow

MySQL UPDATE query with SubQuery

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related