Same situation, but let's say it's not preceding_quarter and current_quarter for the table names. To make the flow of records more clear, let's say the one I was calling preceding_quarter is now called keepers and the one I called current_quarter is now called pickThroughThenDelete. I'll delete the pickThroughThenDelete table after I've found and transferred the new students. Would this syntax work?
insert into keepers select pickThroughThenDelete.* from pickThroughThenDelete left outer join keepers using(student_id) where keepers.student_id is null; drop table pickThroughThenDelete; -----Original Message----- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Friday, June 01, 2007 10:35 AM To: Kebbel, John Cc: mysql@lists.mysql.com Subject: Re: Find and Add Unmatched Records Hi John, Kebbel, John wrote: > I have a table of middle school students I want to update quarterly. I > am only interested in finding and adding new students (not in finding > and deleting students who have been dropped from the school district). > I'm pretty sure I could put together a PHP script that would do this, > but is there a MySQL way for one MySQL table (preceding quarter) to > scan another MySQL table (current quarter) by primary key (student id > number) and absorb the unmatched, new students? I'm thinking that if f > I could bypass PHP, I could use MySQL triggers to automatically add > new records to other, related tables. It sounds to me like you want an "exclusion join": insert into new_students(...) select current_quarter.* from current_quarter left outer join preceding_quarter using(student_id) where preceding_quarter.student_id is null; Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
