MySQL - How can I update a table with values from another table?

Question

Hello,

i'have the task to repair some invalid data in a mysql-database. In one table there are people with a missing date, which should be filled from a second table, if there is a corresponding entry.

TablePeople: ID, MissingDate, ...
TableEvent: ID, people_id, replacementDate, ...

Update TablePeople 
   set missingdate = (select replacementDate 
                        from TableEvent 
                       where people_id = TablePeople.ID)   
where  missingdate is null  
  and (select count(*) 
         from TableEvent 
        where people_id = TablePeople.ID) > 0

certainly doesn't work, is there any other way with SQL? Or how can i process single rows in mysql to get it done?

Answer 1

We need details about what's not working, but I think you only need to use:

UPDATE TablePeople 
   SET missingdate = (SELECT MAX(te.replacementDate)
                        FROM TABLEEVENT te
                       WHERE te.people_id = TablePeople.id)   
 WHERE missingdate IS NULL

Notes

• MAX is being used to return the latest replacementdate, out of fear of risk that you're getting multiple values from the subquery
• If there's no supporting record in TABLEEVENT, it will return null so there's no change