SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    trying to insert with select from same table

    OK, I can insert with a select but in doing this in the mysql query window, I am confused.

    Code MySQL:
    insert into mailing_lists
    select 
      business_id
    , email_address
    , first_name
    from mailing_lists
    where business_id = 101

    But how to I build in an ON DUPLICATE KEY UPDATE clause?

    bazz

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think I need the on duplicate key update now.

    what I am actually trying to do is merge the records from two identical tables, ensuring there are no dupes. The tables are identical but the records aren't completely.

    here's my tables structures

    Code MySQL:
    create table mailing_lists
    ( id int not null
    , email_address varchar(99)
    , first_name varchar(99)
    )

    So far, I have put all the records into the one table, where the difference between old and new, is the ID

    INSERT INTO `mailing_lists` (`business_id`, `email_address`, `first_name`) VALUES
    (456, 'jonny@example.com', 'John Doe'),
    (514, 'jonny@example.com', 'John Doe'),
    (514, 'joe@example.com', 'Joe Soap'),
    (514, 'jimmy@example.com', 'Jimmy Dee'),


    So, my query needs to look in the table for records with one of two ids, 456 OR 514 and convert 514 to 456, where it will not cause a duplicate or error because it has created one.

    So, for the data above, all ids should become 456 but the second record should be overlooked. I can then remove all those with the id of 514 afterwards.

    here is a part query. Am I on the right lines?

    Code MySQL:
    SELECT 
      ML2.business_id
    , ML2.email_address
    , ML2.first_name
    FROM mailing_lists AS ML1
    left outer
    join mailing_lists AS ML2
    on ML1.email_address = ML2.email_address
    where ML1.business_id != ML2.business_id 
    and ML1.business_id = 456
    and ML2.business_id = 514

  3. #3
    SitePoint Member phobucket's Avatar
    Join Date
    Jan 2011
    Location
    Chilladelphia
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you want to have the duplicate records, or do you want to dedupe the records, or are you trying to create a unique mailing list?

    You're last query will return both jonny@example.com records.

    Is this what you are looking for?

    Code MySQL:
    SELECT DISTINCT
      CASE WHEN ML.business_id = 514 THEN 456 ELSE ML.business_id END as business_id 
    , ML.email_address
    , ML.first_name
    FROM mailing_lists AS ML
    WHERE
    ML.business_id IN (456, 514)

    If the id's are not fixed, and you just want to keep the first id, you could do this:

    Code MySQL:
    SELECT 
      MIN(ML.business_id) as business_id 
    , ML.email_address
    , ML.first_name
    FROM mailing_lists AS ML
    GROUP BY 
      ML.email_address
    , ML.first_name

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks phobucket. I got it resolved.

    bazz


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •