Mysql: Delete orphan records

Finding records that do not match between two tables.

          CREATE TABLE bookreport (
            b\_id int(11) NOT NULL auto\_increment,
            s\_id int(11) NOT NULL,
            report varchar(50),
            PRIMARY KEY  (b\_id)

          );

          CREATE TABLE student (
            s\_id int(11) NOT NULL auto\_increment,
            name varchar(15),
            PRIMARY KEY  (s\_id)
          );

          insert into student (name) values ('bob');
          insert into bookreport (s\_id,report)
            values ( last\_insert\_id(),'A Death in the Family');

          insert into student (name) values ('sue');
          insert into bookreport (s\_id,report)
            values ( last\_insert\_id(),'Go Tell It On the Mountain');

          insert into student (name) values ('doug');
          insert into bookreport (s\_id,report)
            values ( last\_insert\_id(),'The Red Badge of Courage');

          insert into student (name) values ('tom');

 To find the sudents where are missing reports:

          select s.name from student s
            left outer join bookreport b on s.s_id = b.s_id
          where b.s_id is null;

              +------+
              | name |
              +------+
              | tom  |
              +------+
              1 row in set (0.00 sec)

 Ok, next suppose there is an orphan record in
 in bookreport. First delete a matching record
 in student:

       delete from student where s_id in (select max(s_id) from bookreport);

 Now, how to find which one is orphaned:

       select * from bookreport b left outer join
       student s on b.s_id=s.s_id where s.s_id is null;

     +------+------+--------------------------+------+------+
     | b_id | s_id | report                   | s_id | name |
     +------+------+--------------------------+------+------+
     |    4 |    4 | The Red Badge of Courage | NULL | NULL |
     +------+------+--------------------------+------+------+
     1 row in set (0.00 sec)

To clean things up (Note in 4.1 you can’t do subquery on same table in a delete so it has to be done in 2 steps):

       select @t_sid:=b.s_id from bookreport b left outer join
         student s on b.s_id=s.s_id where s.s_id is null;

       delete from student where s_id=@t_sid;

 But, functions do work in delete.  For instance the
 following is possible:

        delete from student where s_id=max(s_id);

 It just a problem when joining the table where the
 delete will occur with another table. Another
 option is two create a second temp table and
 locking the first one.