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
