MySQL Update inner Join with MAX Date-CodePudding

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.

CodePudding user response：

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
    )

CodePudding user response：

If your Enrolment table is tracking EnrolmentStatus over time then it would appear that the current relationship between the two tables is the wrong way round.

CREATE TABLE Child (
    ChildId TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    EnrolmentStatus VARCHAR(20) NOT NULL
);

CREATE TABLE EnrolmentStatusLog (
    EnrolmentStatusLogId TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ChildId TINYINT UNSIGNED NOT NULL,
    EnrolmentStatus VARCHAR(20) NOT NULL,
    DateUpdated DATETIME NOT NULL
);

then the query proposed by @groovy_guy just needs the join to be changed

UPDATE  Child
    SET Child.EnrolmentStatus= (
        SELECT EnrolmentStatusLog.EnrolmentStatus
        FROM EnrolmentStatusLog
        WHERE Child.ChildId = EnrolmentStatusLog.ChildId
        ORDER BY DateUpdated DESC
        LIMIT 1
    );

If the above assumptions about structure are correct you could do away with this query by making your changes to Child.EnrolmentStatus directly and using a trigger to populate the EnrolmentStatusLog