MySQL Update a table by min of another table

Question

I would like to update Table Lease from Table History

CREATE TABLE Lease
    (`LeaseID` int, `Name` varchar(3), `Users` varchar(15), `WhoSignID` int, `NoteDate` date)
;
    
INSERT INTO Lease
    (`LeaseID`, `Name`, `Users`, `WhoSignID`, `NoteDate`)
VALUES
    (1, 'AAA', '1000,1001', NULL, NULL),
    (2, 'BBB', '1002', NULL, NULL),
    (3, 'CCC', '1003,1004', NULL, NULL),
    (4, 'DDD', '1005,1006, 1007', NULL, NULL)
;

CREATE TABLE History
    (`HistoryID` int, `LeaseID` int, `User` int, `SignDate` date)
;
    
INSERT INTO History
    (`HistoryID`, `LeaseID`, `User`, `SignDate`)
VALUES
    (1, 1, 1000, '2020-01-05'),
    (2, 1, 1001, '2020-01-04'),
    (3, 1, 1001, '2020-01-02'),
    (4, 1, 1000, '2020-01-03'),
    (6, 2, 1002, '2020-05-01'),
    (7, 2, 1002, '2020-05-03')
;

I looking of a Mysql Update to update Table Lease : NoteDate and WhoSignID based on SignDate and User where Minimum of SignDate of User

Table Lease After Update

LeaseID | Name | Users           | WhoSignID | NoteDate 
1       | AAA  | 1000,1001       | 1001      | 2020-01-02
2       | BBB  | 1002            | 1002      | 2020-05-01
...

I appreciate any assist

question from:https://stackoverflow.com/questions/66057591/mysql-update-a-table-by-min-of-another-table

Answer 1

Your Lease table has a serious design problem, because it is storing users as a CSV list. Instead, you should have each user value on a separate record. That being said, it appears that the CSV user list is immaterial to your current problem, which only required finding the earliest date for each lease. If so, then a simple update join should suffice:

UPDATE Lease l
INNER JOIN
(
    SELECT h1.LeaseID, h1.User, h2.MinSignDate
    FROM History h1
    INNER JOIN
    (
        SELECT LeaseID, MIN(SignDate) AS MinSignDate
        FROM History
        GROUP BY LeaseID
    ) h2
        ON h2.LeaseID = h1.LeaseID AND
           h2.MinSignDate = h1.SignDate
) h
    ON h.LeaseID = l.LeaseID
SET
    WhoSignID = h.User,
    NoteDate = h.MinSignDate;