使用maxdate更新行，其中id来自select，maxdate来自select

Question

我有一个select语句，它返回一组I和一个maxdate。现在，我想用给定的最大值更新发票表中的performance_date，其中指定的发票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

(如何)这在MySQL中是可能的？

Answer 1

您可以使用当前的SELECT查询作为导出表，并使用id对invoices表进行Join，然后进行更新。

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