2

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?

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
Thomas G. Liesner
  • 21
  • 2
  • 1
    Is there a maximimum of one record per `people_id` in `TableEvent`? If not which do you want used? You say your current query doesn't work. Do you mean syntactically or semantically? – Martin Smith Aug 01 '10 at 22:54

1 Answers1

2

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
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Thx, problem is solve after one Correction: "WHERE te.people_id = TablePeople.id" instead of "WHERE te.people_id = id". My main problem was, that i was absolutely sure, that the subquery can't use field from the main table. – Thomas G. Liesner Aug 01 '10 at 23:16