CODEWITHSUNDEEP
mysqlsqlsql-update
Thomas G. Liesner
Thomas G. Liesner

Reputation: 21

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

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?

Upvotes: 2

Views: 2075

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332571

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

Upvotes: 2

Related Questions