使用带有 WHERE 条件的单个查询 (SQL Express 2005) 从多个表中删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1714545/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete rows from multiple tables using a single query (SQL Express 2005) with a WHERE condition
提问by Jobi
This is the query I'm using:
这是我正在使用的查询：
DELETE TB1.*, TB2.*
FROM TB1
INNER JOIN TB2 ON TB1.PersonID = TB2.PersonID
WHERE (TB1.PersonID)='2'
It's working fine in MS Access but getting error (Incorrect syntax near ','.) in SQL Server Express 2005.
它在 MS Access 中运行良好，但在 SQL Server Express 2005 中出现错误（',' 附近的语法不正确。）。
How to solve it? Please help.
如何解决？请帮忙。
回答by Matijs
You cannot DELETEfrom multiple tables with a single expression in SQL 2005- or any other standard SQL for that matter. Accessis the exception here.
您不能DELETE从多个表中使用单个表达式 in SQL 2005- 或任何其他标准 SQL。Access是这里的例外。
The best method to get this effect is to specify FOREIGN KEYSbetween the table with an ONDELETEtrigger.
获得这种效果的最佳方法是FOREIGN KEYS在表之间用ONDELETEtrigger.
回答by Cesar
Why you don't use a DELETE CASCADE FK?
你为什么不使用DELETE CASCADE FK?
回答by Adriaan Stander
This cannot be done in one statement. You will have to use 2 statements
这不能在一个声明中完成。您将不得不使用 2 个语句
DELETE FROM TB1 WHERE PersonID = '2';
DELETE FROM TB2 WHERE PersonID = '2';
回答by j.a.estevan
As i know, you can't do it in a sentence.
据我所知，你不能用一句话来做到这一点。
But you can build an stored procedure that do the deletes you want in whatever table in a transaction, what is almost the same.
但是您可以构建一个存储过程，在事务中的任何表中执行您想要的删除操作，这几乎是相同的。
回答by Alex Deem
I don't think you can delete from multiple tables at once (though I'm not certain).
我认为您不能一次从多个表中删除（尽管我不确定）。
It sounds to me, however, that you would be best to achieve this effect with a relationship that cascades deletes. If you did this you would be able to delete the record from one table and the records in the other would be automatically deleted.
然而，在我看来，最好通过级联删除的关系来实现这种效果。如果您这样做，您将能够从一个表中删除记录，而另一个表中的记录将被自动删除。
As an example, say the two tables represent a customer, and the customer's orders. If you setup the relationship to cascade deletes, you could simply delete record in the customer table, and the orders would get deleted automatically.
举个例子，假设两个表代表一个客户，以及客户的订单。如果将关系设置为级联删除，则只需删除客户表中的记录，订单就会自动删除。
See the MSDN doc on cascading referential integrity constraints.
请参阅有关级联引用完整性约束的 MSDN 文档。
回答by Himadri
Specify foreign key for the details tables which references to the primary key of master and set Delete rule = Cascade .
为详细信息表指定外键，引用 master 的主键并设置 Delete rule = Cascade 。
Now when u delete a record from the master table all other details table record based on the deleting rows primary key value, will be deleted automatically.
现在，当您从主表中删除一条记录时，所有其他基于删除行主键值的明细表记录都将被自动删除。
So in that case a single delete query of master table can delete master tables data as well as child tables data.
因此，在这种情况下，主表的单个删除查询可以删除主表数据以及子表数据。
回答by Josh Withee
I use this for cleaning up data in test/development databases. You can filter by table name and record count.
我用它来清理测试/开发数据库中的数据。您可以按表名和记录数进行过滤。
DECLARE @sqlCommand VARCHAR(3000);
DECLARE @tableList TABLE(Value NVARCHAR(128));
DECLARE @TableName VARCHAR(128);
DECLARE @RecordCount INT;
-- get a cursor with a list of table names and their record counts
DECLARE MyCursor CURSOR FAST_FORWARD
FOR SELECT t.name TableName,
i.rows Records
FROM sysobjects t,
sysindexes i
WHERE
t.xtype = 'U' -- only User tables
AND i.id = t.id
AND i.indid IN(0, 1) -- 0=Heap, 1=Clustered Index
AND i.rows < 10 -- Filter by number of records in the table
AND t.name LIKE 'Test_%'; -- Filter tables by name. You could also provide a list:
-- AND t.name IN ('MyTable1', 'MyTable2', 'MyTable3');
-- or a list of tables to exclude:
-- AND t.name NOT IN ('MySpecialTable', ... );
OPEN MyCursor;
FETCH NEXT FROM MyCursor INTO @TableName, @RecordCount;
-- for each table name in the cursor, delete all records from that table:
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlCommand = 'DELETE FROM ' + @TableName;
EXEC (@sqlCommand);
FETCH NEXT FROM MyCursor INTO @TableName, @RecordCount;
END;
CLOSE MyCursor;
DEALLOCATE MyCursor;
Reference info:
参考资料：
回答by Simbo
Use this in procedure
在程序中使用它
declare cu cursor for SELECT [name] FROM sys.Tables where [name] like 'tbl_%'
declare @table varchar(100)
declare @sql nvarchar(1000)
OPEN cu
FETCH NEXT FROM cu INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = N'delete from ' + @table
EXEC sp_executesql @sql
FETCH NEXT FROM cu INTO @table
END
CLOSE cu;
DEALLOCATE cu;
回答by Ashvin patidar
$qry = "DELETE lg., l.FROM lessons_game lg RIGHT JOIN lessons l ON lg.lesson_id = l.id WHERE l.id = ?";
$qry = "DELETE lg. , l.FROM classes_game lg RIGHT JOIN classes l ON lg.lesson_id = l.id WHERE l.id = ?";
lessons is Main table and lessons_game is subtable so Right Join
课程是主表，课程是子表，所以右加入
回答by Yablargo
You can use something like the following:
您可以使用以下内容：
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ("TB2","TB1") -- use these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM @name WHERE PersonID ='2'
FETCH NEXT FROM db_cursor INTO @name
END
