Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2017
    Posts
    2

    Unanswered: How to remove same records which are present in two different columns of same table.

    How to remove same records which are present in two different columns of same table called "DUPLICATE" as shown below. I need the desired output as mentioned below. Thanks in advance....

    col1 col2
    ------ -----
    banglore | chennai

    kolkata | pune

    chennai | mysore

    pune | banglore

    mysore |kolkata

    mumbai |delhi


    desired o/p:-
    ------------
    col1 | clo1
    ------- | -------

    banglore |chennai

    kolkata | pune

    mumbai | delhi

  2. #2
    Join Date
    Jun 2004
    Posts
    812
    Provided Answers: 1
    How are you deciding which duplicate record to delete? Your example doesn't make it clear.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Mar 2017
    Posts
    2

    reply for the comment..

    "How are you deciding which duplicate record to delete? Your example doesn't make it clear"

    Firstly I would like to thank you for a quick reply. But I am sorry if my question was not clear. I faced this question in one of my interview. I wasn't able to solve this.. and i don't know how they decided the output to be.. sorry for that. Can I get the answer for it..?

  4. #4
    Join Date
    Jun 2004
    Posts
    812
    Provided Answers: 1
    As it stands the question "How to remove same records which are present in two different columns of same table?" will not result in any records being deleted. Each record consists of 2 columns (col1 & col2) - there are no duplicate (or 'same') records as no 2 records match in both col1 AND col2. What you have is a table of records where some of them have matching (or 'same') data in either of the 2 columns. "Duplicate records" & "matching data" are completely different.

    If I had been asked this in an interview I would have asked for more information from the interviewer - specifically "How do I determine which record is a duplicate?" and (just as importantly) "How do I determine which of the duplicate records should be the one to be deleted".
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  5. #5
    Join Date
    Aug 2017
    Posts
    6
    You can use this SQL:

    select col1,col2 from DUPLICATE where col2 not in (select col1 from DUPLICATE)

  6. #6
    Join Date
    Jun 2004
    Posts
    812
    Provided Answers: 1
    Quote Originally Posted by Richardto View Post
    You can use this SQL:

    select col1,col2 from DUPLICATE where col2 not in (select col1 from DUPLICATE)
    This doesn't solve the original question, which was how to REMOVE duplicate records.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Tags for this Thread

Posting Permissions

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