#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    10
    Rep Power
    0

    Delete dupes with Join


    I hope someone can help me with this. I'm trying to delete duplicate records from one table by joining another with a condition. For example:

    info_table

    InfoId, email, firstname, lastname

    100, kris1ATyahoo.com, kris, Kot

    101, kris1ATyahoo.com, kris, Kot

    102, kirs1ATyahoo.com, kris, Kot

    103, daveATyahoo.com, dave, Snider

    104, daveATyahoo.com, dave, snider

    105, tomATyahoo.com, tom, sawyer

    106, tomATyahoo.com, tom, sawyer

    tran_table

    id, infoId, status

    1, 100, 1

    2, 101, 2

    3, 102, 1

    4, 103, 2

    5, 104, 1

    6, 105, 1

    7, 106, 1

    So, I want to delete from info_table with the below conditions.

    I want to delete all the duplicate(by email) from infotable. However, I want to keep those whose status in tran_table is '2'.

    Meaning, I want to delete the below:

    Among 100,101,102 -- delete all except 101

    Among 103,104 -- delete 104

    Among 105, 106 -- delete any

    Appreciate any help with the query--
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1045
    Hi,

    I don't really see what this has do to with duplicates. As far as I can tell, you simply remove all records with status <> 2, even if that doesn't leave any record for a given email address (like in your third case).

    Anyway, check the MySQL manual for the USING clause of the DELETE statement. It allows you to join a table you want to delete from with other auxiliary tables.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    10
    Rep Power
    0
    Only difference is, I do not want to delete all the rows for 3rd case. I need to keep one of those records.


    Originally Posted by Jacques1
    Hi,

    I don't really see what this has do to with duplicates. As far as I can tell, you simply remove all records with status <> 2, even if that doesn't leave any record for a given email address (like in your third case).

    Anyway, check the MySQL manual for the USING clause of the DELETE statement. It allows you to join a table you want to delete from with other auxiliary tables.

IMN logo majestic logo threadwatch logo seochat tools logo