如果表B中有数据，如何删除表A中的数据？ [英] How to delete the data in Table A if the data is available in Table B?

问题描述

假设我在2个数据库表中有更多数据。我的问题是如果在表B中找到行/数据，如何删除表A中的行/数据？如何比较它们以及如何删除表A到表B中的重复数据。

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.

推荐答案

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

我建议使用存在而不是在中，因为 EXISTS 会更快，因为一旦引擎找到命中，它就会退出看起来条件证明是正确的。使用IN它将在进一步处理之前从子查询中收集所有结果



测试：

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:

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 [ ^ ]

HI，



我建议您使用表A中的IsDeleted列来删除项目。我建议你这个解决方案，因为如果来自表A的数据在任何其他表中都没有被称为外键，那么数据可以被删除但是如果数据作为外键被映射到其他表中那么那将永远不会被删除而你麻烦。



因此，养成在每个表中添加IsDeleted（boolean）列的习惯，如果要删除记录，请将值设置为已删除。这样你就可以在删除查询中管理外键冲突。



谢谢

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

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

这篇关于如果表B中有数据，如何删除表A中的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！