Mysql Question posted in
The official MySQL documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    With sample data as:

    Create Table CUSTOMERS (REC_NO INT, CUST_KEY INT, MAINT_DATE DATE);

Insert Into CUSTOMERS 
  Select 1, 100, DATE '2023-12-01' From Dual Union All
  Select 2, 200, DATE '2023-12-10' From Dual Union All
  Select 3, 300, DATE '2023-12-20' From Dual 

Select * From CUSTOMERS
/*
REC_NO  CUST_KEY    MAINT_DATE
    1       100     2023-12-01
    2       200     2023-12-10
    3       300     2023-12-20      */

    Create Table SERVICE_ORDERS (REC_NO INT, CUST_KEY INT, SERVICE_DATE DATE);

Insert Into SERVICE_ORDERS 
  Select 1, 100, DATE '2023-12-02' From Dual Union All
  Select 2, 100, DATE '2023-12-12' From Dual Union All
  Select 3, 200, DATE '2023-12-15' From Dual Union All
  Select 4, 300, DATE '2023-12-28' From Dual Union All
  Select 5, 300, DATE '2024-01-04' From Dual Union All
  Select 6, 300, DATE '2024-01-11' From Dual 

Select * From SERVICE_ORDERS
/*
REC_NO  CUST_KEY    SERVICE_DATE
    1       100     2023-12-02
    2       100     2023-12-12
    3       200     2023-12-15
    4       300     2023-12-28
    5       300     2024-01-04
    6       300     2024-01-11      */

    U p d a t e :

    UPDATE CUSTOMERS c 
SET c.MAINT_DATE = (Select Max(SERVICE_DATE) 
                    From SERVICE_ORDERS 
                    Where CUST_KEY = c.CUST_KEY
                   )
WHERE EXISTS (Select 1 From SERVICE_ORDERS Where CUST_KEY = c.CUST_KEY)

    Check the result:

    Select * From CUSTOMERS
/*
REC_NO  CUST_KEY    MAINT_DATE
    1       100     2023-12-12
    2       200     2023-12-15
    3       300     2024-01-11      */
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search