Yesmin Patwary <yesmin25@stripped> 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:
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!
Unimin Corporation - Spruce Pine