CODEWITHSUNDEEP
mysqlsql-update
DS87
DS87

Reputation: 602

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

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?

Upvotes: 2

Views: 105

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28854

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

Upvotes: 1

Related Questions