List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 22 2006 3:47pm
Subject:Re: INSERT...SELECT Query Help Request.
View as plain text  
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:
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


Thread
Compare lists Query?Yesmin Patwary10 Mar
  • Re: Compare lists Query?Josh10 Mar
    • Re: Compare lists Query?Yesmin Patwary13 Mar
      • Re: Compare lists Query?Peter Brawley13 Mar
      • Re: Compare lists Query?Josh13 Mar
        • Problems with timestamp field after upgrading MySQL Server.Yesmin Patwary13 Mar
          • Re: Problems with timestamp field after upgrading MySQL Server.Josh13 Mar
            • Re: Problems with timestamp field after upgrading MySQL Server.SGreen13 Mar
        • Problems with timestamp field after upgrading MySQL Server.Yesmin Patwary13 Mar
        • INSERT...SELECT Query Help Request.Yesmin Patwary22 Mar
          • Re: INSERT...SELECT Query Help Request.SGreen22 Mar