mysql - 使用 maxdate 更新行，其中给出了 id from select 和 maxdate from select

Question

我有一个返回一组 id 和一个 maxdate 的 select 语句。现在，我想用给定发票 ID 的给定最大日期更新发票表中的 performance_date。

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 查询用作 Derived Table并使用id将其加入到invoices表，然后更新。

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