INSERT...SELECT Query Help Request.

2006-03-22 Thread Yesmin Patwary 
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.


-
Yahoo! Mail
Bring photos to life! New PhotoMail  makes sharing a breeze.

Re: INSERT...SELECT Query Help Request.

2006-03-22 Thread SGreen 
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