How to delete the data in Table A if the data is available in Table B?

Question

0.00/5 (No votes)

See more:

SQL-server-2000

, +

Assuming i have more data in to 2 database table.My problem is How to delete the row/data in Table A if the row/data found in Table B? How to compare them and how to delete the duplicate data in table A to Table B.

Posted 29-Jul-13 21:18pm

Gishisoft

Updated 29-Jul-13 21:19pm

v2

Add a Solution

Comments

Maciej Los 30-Jul-13 3:25am

Please, be more specific and provide more details (example data). We can't read in your mind ;(
What have you tried till now?

Gishisoft 30-Jul-13 3:34am

Just like what Winston Madiano did. thats what i want.

Maciej Los 30-Jul-13 3:35am

Who?

Gishisoft 30-Jul-13 3:35am

It's just like a recursive time of search and delete from one table to another table.

Gishisoft 30-Jul-13 3:38am

check below. the one who give his solution.

3 solutions

Solution 3

HI,

I would suggest you for the IsDeleted column in table A for the items to delete. I am suggesting you this solution because if the data from the table A is not referred as foreign key in any other table then the data can be deleted but if the data is mapped in other table as foreign key then that will never be deleted and you will be in trouble.

So make a habit of adding the IsDeleted (boolean) column in each table and set the value there as deleted if you want to delete the records. In this way you can manage the foreign key conflicts as well in the delete query.

Thanks

Posted 29-Jul-13 21:34pm

Sisir Patro

Solution 1

SQL

DELETE FROM [Table A] WHERE [ID] IN (SELECT [ID] FROM [Table B]);

Posted 29-Jul-13 21:23pm

Winston Madiano

Updated 29-Jul-13 21:32pm

v2

Comments

Gishisoft 30-Jul-13 3:27am

Ok i will try this one.

Gishisoft 30-Jul-13 3:46am

oopps. is there any another option? it doesn't work. :( some peace is missing.

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Raja Sekhar S · Accepted Answer · 2013-07-29T21:45:00

Solution 4

SQL

Delete from #T1 Where exists(Select Id From #T2 where #T2.Id=#T1.Id)

I suggest Using exists Instead of In because EXISTS will be faster because once the engine has found a hit, it will quit looking as the condition has proved true. With IN it will collect all the results from the subquery before further processing

Tested:

VB

Create table #T1 (Id int)
Create table #T2 (Id int)
Insert into #T1
Select 1 Union all
Select 2 Union all
Select 3 Union all
Select 4 Union all
Select 5

Insert into #T2
Select 1 Union all
Select 2 Union all
Select 3 

Delete from #T1 Where exists(Select Id From #T2 where #T2.Id=#T1.Id)

Select * From #T1 -- Returns 4,5

Drop Table #T1
drop Table #T2

http://www.techonthenet.com/sql/delete.php[^]

Posted 29-Jul-13 21:45pm

Raja Sekhar S

Updated 30-Jul-13 1:25am

v5

Comments

Maciej Los 30-Jul-13 3:52am

+5Good answer!

Raja Sekhar S 30-Jul-13 4:08am

Thanks Maciej Los!....

Gishisoft 30-Jul-13 4:02am

sorry it won't work. it will not search the data and delete it.

Gishisoft 30-Jul-13 4:06am

i used variable table for this. but nothings happen. :(

Raja Sekhar S 30-Jul-13 4:08am

Can u Post the Query Which u Used..?

Gishisoft 30-Jul-13 4:28am

Table 1
id|col1|col2|col3
10059 | PRTP001 | 61020221 | thread
8249 | PRT001| 60041686| weftknitted
9896 |PRT001| 61016477| eyelet
.
.
.

Table 2
id|col1|col2|col3
10059 |61020221 |WORD |Group 1 (PR001)
8242 |60041686| WORD |Group 1 (PR001)
9896 |61016477| EXCEL| Group 1 (PR001)
.
.
.

Gishisoft 30-Jul-13 4:32am

Table 1 is a variable table. so when i compare it to Table2 all id found in table 1 equal to table2 will delete the row/data.

so in the example above. only "8249 | PRT001| 60041686|weftknitted" will be the remaining so on and so forth.

Raja Sekhar S 30-Jul-13 4:45am

Can u Post the Query u used...?

Gishisoft 30-Jul-13 4:46am

when i used static type of delete like "DELETE FROM @Table1 WHERE id = '10059'" but if i try recursive type. it can't. nothings happen.

Gishisoft 30-Jul-13 4:52am

DELETE FROM @Table1 WHERE EXISTS (SELECT Table2.id,Table2.Col1 FROM Table2 WHERE id = Table2.id AND col2 = Table2.col1)

Gishisoft 30-Jul-13 4:53am

nothings happen.

Gishisoft 30-Jul-13 5:11am

try to apply it. using that info only. but in dynamic way not with static type of codes. it gives me a headache. how to solve it.

[no name] 30-Jul-13 10:50am

So you want to delete all the 3 records from teh table 1 displayed here in the table structure right?

Raja Sekhar S 30-Jul-13 6:10am

Check the Updated Solution....

Raja Sekhar S 30-Jul-13 7:25am

Don't Use Table Variable instead use temporary table..

How to delete the data in Table A if the data is available in Table B?

3 solutions

Solution 4

Solution 3

Solution 1

Add your solution here

Preview 0

How to delete the data in Table A if the data is available in Table B?

3 solutions

Solution 4

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us