I get the data I need with the following select statement:

SELECT tk.actiondate, tk.completedate, tk.closedate, MAX(com.date) AS realcomplete, MAX(clo.date) AS realclose

FROM ticket tk

LEFT JOIN ticketaction com ON (com.idticket = tk.idticket AND com.note LIKE 'Completed')
LEFT JOIN ticketaction clo ON (clo.idticket = tk.idticket AND clo.note LIKE 'Closed')

GROUP BY tk.idticket;

Now I would like to update the ticket table like this:

SET tk.completedate = realcomplete,
    tk.closedate = realclose

Any push in the right direction will be greatly appreciated...

  • UPDATE ticket JOIN ( {your query} ) AS subquery SET ticket.column = subquery.column WHERE ticket.id = subquery.id. PS. If idticket is not unique then your SELECT is wrong.
    – Akina
    May 17 at 16:13


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.