 |
|
 |
| |
Developer on focus
Anton Zamov is a dipl. engineer with more than 6 years of active professional
experience and many awards ...
read more>>
|
|
 |
 |
 |
|
|
|
|
| |
|
MySQL: 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.
|
About the author of this programming example or tutorial:
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (c) 2004 (GPU Free Documentation License)
Last Updated: Tue Jul 20 12:14:51 EDT 2004
|
|
|
|
|
|
|
|
