Deleting rows from table based on value from other table

suddublr · #1 (**permalink**) 11-23-11, 10:43

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

anacedent · #2 (**permalink**) 11-23-11, 10:55

>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.

suddublr · #3 (**permalink**) 11-23-11, 11:25

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...