Update row with maxdate where id from select and maxdate from select is given

Question

I have a select statement that returns a set of ids and a maxdate. Now, I want to update the performance_date in the invoices table with the given maxdate where the given invoices ids.

Select invoices.id, max(invoicepositions.performance_date) as maxdate
from invoices
inner join invoicepositions on invoices.id = invoicepositions.invoice_id
where invoices.performance_date IS NULL
group by invoices.id

(How) is this possible with MySQL?

score 1 · Accepted Answer · answered Nov 23 '18 at 21:48

You can use your current SELECT query as a Derived Table and Join it to the invoices table using id, and then update.

UPDATE invoices AS i 
JOIN
(
  Select invoices.id, max(invoicepositions.performance_date) as maxdate
  from invoices
  inner join invoicepositions on invoices.id = invoicepositions.invoice_id
  where invoices.performance_date IS NULL
  group by invoices.id
) AS dt 
  ON dt.id = i.id 
SET i.performance_date = dt.maxdate

Update row with maxdate where id from select and maxdate from select is given

1 Answers1