Yesmin Patwary [EMAIL PROTECTED] wrote on 03/22/2006 09:56:20 AM:
Dear All,
First of all, I would like to thank Shawn Green, Peter Brawley and
Josh for their kind help in my previous issue.
I have a table named master_list with two field customer_id and
list_code. I need to insert only non existent records in
master_list from a child_list. Both master_list and child_list table
structure are identical but data in child_list may contain records
from master_list and new records.
I have constructed query using INSERT…SELECT but I am unable to
check and filter out records that already exist in master_list.
INSERT INTO master_list (customer_id,list_code) SELECT DISTINCT
customer_id,list_code FROM child_list
Is there anyway to check and insert records in master_list without
creating dups?
Thanks in advance for any help.
If you have a unique key or primary key set up on
(master_list.customer_id, master_list.list_code) it's more simple that you
think. Just add the word IGNORE to your INSERT statement like this :-)
INSERT IGNORE INTO master_list (customer_id,list_code) SELECT DISTINCT
customer_id,list_code FROM child_list
usage details are here:
http://dev.mysql.com/doc/refman/5.0/en/insert.html
The IGNORE will tell the engine to disregard all duplicate key errors
and continue processing rows. If you don't have such a key, I suggest you
add one or let us know why you can't create it. Which workaround we can
use for the lack of the key will depend on the version you are using.
You are most welcome!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine