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.
  • Escoffie

    Hello:
    It works, but i did’nt understood somthing. What the @t_sid: means in the “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;” query?
    Thank you.

  • Escoffie

    Hello:
    It works, but i did'nt understood somthing. What the @t_sid: means in the “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;” query?
    Thank you.

  • Escoffie

    Hello:
    It works, but i did'nt understood somthing. What the @t_sid: means in the “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;” query?
    Thank you.