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.

Mysql: Loading data from file

Loading Data into Tables from Text Files.

Assume you have the following table.

            CREATE TABLE loadtest (
                pkey int(11) NOT NULL auto_increment,
                name varchar(20),
                exam int,
                score int,
                timeEnter timestamp(14),
                PRIMARY KEY  (pkey)
               );

And you have the following formatted text file as shown below with the unix "tail" command:

          $ tail /tmp/out.txt
    'name22999990',2,94
    'name22999991',3,93
    'name22999992',0,91
    'name22999993',1,93
    'name22999994',2,90
    'name22999995',3,93
    'name22999996',0,93
    'name22999997',1,89
    'name22999998',2,85
    'name22999999',3,88

    NOTE: loadtest contains the "pkey" and "timeEnter" fields which are not
    present in the "/tmp/out.txt" file. Therefore, to successfully load
    the specific fields issue the following:

         mysql> load data infile '/tmp/out.txt' into table loadtest
                  fields terminated by ',' (name,exam,score);

Mysql: (date) functions and indexes

Mysql is usually pretty fast by default, but to keep performance to the max sometimes requires knowledge on how mysql works and how to write queries that does their job most efficiently. Today we ran across a simple example which illustrates that mysql’s ability to use an index depends on the way you write the query.
Continue reading Mysql: (date) functions and indexes

SQL and beyond

SQL is a common language implemented by most databases. While it’s a nice language it does lack some features available in some databases (which often differ from database to database). Some argue, that you shouldn’t go beyond the contrains and limitations in SQL because that removes your ability to switch to a different database. That’s just wrong (in my opinion).
Continue reading SQL and beyond