Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    7

    Unanswered: Deleting rows from table based on value from other table

    Hello Members,

    I am struck to solve the issue said below using query. Would appreciate any suggestions...

    I have two tables having same structures. I want to delete the rows from TableA ( master table ) with the values from TableB ( subset of TableA). The idea is to remove the duplicate values from tableA. The data to be removed are present in TableB. Catch here is TableB holds one row less than TableA, for example
    Table A
    --------
    Name Value
    -------------
    Test 1
    Test 1
    Test 1
    Hello 2
    Good 3

    TableB
    -------------
    Name Value
    ----------------
    Test 1
    Test 1

    The goal here is to remove the two entries from TableB ('Test') from TableA, finally leaving TableA as
    Table A
    --------
    Name Value
    -------------
    Test 1
    Hello 2
    Good 3

    I tried below queries
    1. delete from TestA a where rowid = any (select rowid from TESTA b where b.Name = a.Name and a.Name in ( select Name from TestB ));

    Any suggestions..

    Thanks in advance

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The idea is to remove the duplicate values from tableA.
    Then why is tableB needed?
    Why do duplicate value exist within tableB? Does TableB need to have duplicates removed from it?
    If not, why not.
    P.S.
    Lousy homework assignment.
    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.

  3. #3
    Join Date
    Nov 2011
    Posts
    7
    Thanks.

    Well, the issue I mentioned is a part of the process. The thing is TableB contains the duplicate values which should be deleted from TableA. So that we know what all values we have deleted from TableA. On deleted TableA if I later insert the value from TableB I should be getting the original TableA...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

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
  •