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

    Join Date
    Dec 2017
    Posts
    2
    Rep Power
    0

    Query for deleting duplicate records


    Hi,
    Need a help regarding query for deleting duplicate records.
    In my table,I have duplicate entries for complaints against each doctor. So,to delete these duplicate complaints against each doctor,doing a Group by on 2 columns.
    There is no error but query is doing nothing(affecting zero records).
    Below is the query:-

    delete from tbl_illness_master where id NOT IN (select id from (select max(n.id),count(name),doc_id from tbl_illness_master n group by n.name,n.doc_id) x)


    Running this on phpMyAdmin


    Pls help!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,757
    Rep Power
    4288
    first of all, i don't believe there is no error, because subquery x does not have a column called id

    but more importanlty, You cannot delete from a table and select from the same table in a subquery

    you'll have to create a temp table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2017
    Posts
    2
    Rep Power
    0
    Originally Posted by r937
    first of all, i don't believe there is no error, because subquery x does not have a column called id

    but more importanlty, You cannot delete from a table and select from the same table in a subquery

    you'll have to create a temp table
    Thanks so much for the reply! In case I write above query as,

    delete from tbl_illness_master where id NOT IN (select id from (select max(n.id) from tbl_illness_master n group by n.name,n.doc_id) x)

    Doesn't subquery x now returning column 'id' to outer query? Max(n.id) ultimately is 'id'. Also,does it mean that above query wont work in MySQL but would work in other databases.
    And apart from using temp table,there is no way to delete duplicates when doing group by on two columns.

    Waiting to hear from you..


    Thanks,
    Manisha
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,757
    Rep Power
    4288
    Originally Posted by manisha12
    Doesn't subquery x now returning column 'id' to outer query? Max(n.id) ultimately is 'id'.
    well, you could test it yourself...
    Code:
    SELECT id 
      FROM ( SELECT MAX(n.id) 
               FROM tbl_illness_master n 
             GROUP 
                 BY n.name
                  , n.doc_id ) x
    what do you get?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo