Find and Add Unmatched Records
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.
RE: Find and Add Unmatched Records
The REPLACE staement might work for you... http://dev.mysql.com/doc/refman/4.1/en/replace.html... -Original Message- From: Kebbel, John [mailto:[EMAIL PROTECTED] Sent: 01 June 2007 15:31 To: mysql@lists.mysql.com Subject: Find and Add Unmatched Records 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. This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
RE: Find and Add Unmatched Records
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]
Re: Find and Add Unmatched Records
Hi John, Yes, that looks right to me. Baron Kebbel, John wrote: 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 -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unmatched records
I am trying to compare two tables and get a list of all the data that does not match. How can I compare all the fields from two tables with one SQL statement? I've been using the Find Unmatched Query Wizard, but it will only let me compare one field at a time. I want to list all the ID numbers that have differing info in any field from two tables. Jac - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
