update maintenance date in customer table from the latest date in service table

Question

Customers

Field	Type	Null	Key	Default	Extra
recno	int	NO	PRI	NULL	auto_increment
CustomerKey	int	NO		NULL
MaintDate	date	YES		NULL

Service Orders

Field	Type	Null	Key	Default	Extra
recno	int	NO	PRI	NULL	auto_increment
CustomerKey	int	NO		NULL
ServiceDate	date	YES		NULL

Customers has one record per customer. Service Orders has multiple records per customer.

How do I UPDATE Customers to update each Customers MaintDate record with the record in Service Orders with the newest ServiceDate?

Answer 1

A grouped join is likely more efficient than the subquery plus EXISTS, as that answer looks up the child table twice.

UPDATE Customers c
JOIN (
    SELECT
      so.CustomerKey,
      MAX(SERVICE_DATE) AS MaxDate
    FROM ServiceOrders so
    GROUP BY so.CustomerKey
) so ON so.CustomerKey = c.CustomerKey 
SET c.MaintDate = so.MaxDate;

Having said that, I would advise you not to denormalize like this, and instead just query the child table when needed, unless such a query is particularly inefficient.