Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2013
    Posts
    5

    Red face Unanswered: Delete a similar looking row

    Hi Forum,

    I am a new Oracle developer and am stuck with one scenario.
    My table has rows about employee information having id, name, age.
    I see some duplicate data like:

    ID NAME AGE
    01 david 25
    02 davids 25
    03 mike 27
    04 mikes 28

    Not all records are like this. There are a few of them. How can I delete the one with the 's'

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    It looks like Mike and Mikes are two different people (they have different ages). I doubt that your scenario is as simple as you post, are you sure that the definition of a duplicate is that if it has a last letter of s removed, and another row has that same value, then it's a duplicate?
    If so, then which of the two rows do you keep?
    id 1 or 2 for David(s)
    id 3 or 4 for Mike(s)

  3. #3
    Join Date
    Oct 2013
    Posts
    5
    Hi Pablolee, lets talk of the David rows. I am 100% sure that they are the same person. Just want to remove the name with an 's' behind it. I can remove any row as the dependent functions performed on the table are by name, not by the id.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Perhaps something like this?

    air-code
    Code:
    DELETE
    FROM   people As p_s
    WHERE  Right(p_s.name, 1) = 'S'
    AND    EXISTS (
             SELECT *
             FROM   people As p_no_s
             WHERE  Right(p_no_s.name, 1) <> 'S'
             AND    p_no_s.name + 'S' = p_s.name
           )
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by get2harry View Post
    Hi Pablolee, lets talk of the David rows. I am 100% sure that they are the same person. Just want to remove the name with an 's' behind it. I can remove any row as the dependent functions performed on the table are by name, not by the id.
    OK, so the david rows are a 'match'. Fine. Now let's talk about the Mike rows.

  6. #6
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by gvee View Post
    Perhaps something like this?
    Ready FIRE, ... Aim
    Yes, perhaps air code like that might work. But then, perhaps it won't, because we haven't actually been given a concrete set of rules yet have we? Surely it would be better to trie to get to the bottom of the requirements before trying to proffer a solution to a problem that is not currently fully detailed or understood?

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    unless & until you can post SELECT which returns all "duplicate" rows
    we can not provide to DELETE all but one of them; since I assume 2 OR MORE duplicates could exist.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Oct 2013
    Posts
    5
    Hi everyone, Thank you for your replies.

    I assure you that there are just 2 records. One original one, and one is the duplicate one - the one with the 's'. Isn't it possible in anyway?

    Here is the actual scenario. -- Attached the picture. I hope it is clearer now.
    Attached Thumbnails Attached Thumbnails AA.png  

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DELETE FROM TABLE1 WHERE TEMPLATE LIKE '%templates%';
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Oct 2013
    Posts
    5
    Hi Anacedent, the problem is that suppose there exists only one record of 'templates', but it has no corresponding 'template' - we shouldn't be deleting it then, because we need the name. The thing here is that we should have either template or templates. But not both.
    Last edited by get2harry; 10-21-13 at 16:41. Reason: edit

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by get2harry View Post
    Hi Anacedent, the problem is that suppose there exists only one record of 'templates', but it has no corresponding 'template' - we shouldn't be deleting it then, because we need the name. The thing here is that we should have either template or templates. But not both.
    How do you determine which file actually exists?
    You could arbitrarily DELETE the wrong record & have the remaining record listing non-existent file.

    so how do you decide between the two "duplicate" records, which record to keep & which to DELETE?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Oct 2013
    Posts
    5
    Hi, ideally the templates.dwt shouldn't be there in the first place. So if it is there, but there is also a template.dwt record for the same NAME, then we delete it (templates.dwt).

    The second scenario is, there is a templates.dwt, but there is no template.dwt for the same NAME. So we need to keep the templates.dwt in that case.

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
  •