MySQL Update inner Join with MAX Date

In MYSQL 8.0 I have two tables with following structure:

Table1 :Child
ChildId | EnrolmentId | EnrolmentStatus

Table 2 : Enrolment
EnrolmentId | EnrolmentStatus | DateUpdated    

I am trying to update a value of EnromentStatus in child table by joining in on Enrolment table using the TSQL below:

UPDATE child
INNER JOIN enrolment ON child.enrolmentid= enrolment.enrolmentid
SET child.EnrolmentStatus = enrolment.enrolmentstatus 
WHERE child.enrolmentid = enrolment.enrolmentid;

Problem is Enrolment has multiple entries for the child enrolment so I need to do MAX(DateUpdated) to get just a single record to update the status to latest status but I can’t wrap my head around to just get one record.

Answer

Try something like this,
It’s just a simple nested query that updates all of the child’s statuses in one go without using INNER JOIN.

UPDATE  child
    SET child.enrolmentstatus= (
        SELECT enrolment.enrolmentstatus
        FROM enrolmentstatus
        WHERE child.enrolmentid = enrolment.enrolmentid
        ORDER BY DateUpdated DESC
        LIMIT 1
    )