blocks|key|1410419|text|为什么不使用DELETE+CASCADE+FK？|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1410420|entityMap^0|6|H|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

Why you don't use a <code>DELETE CASCADE FK</code> ?

blocks|key|1195151|text|您不能在SQL+2005或任何其他标准DELETE中使用单个表达式从多个表执行SQL。Access在这里是个例外。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1195152|获得此效果的最佳方法是使用ON+DELETE+trigger在表之间指定FOREIGN+KEYS。|1195153|entityMap^0|4|8|J|6|17|6|0|D|2|G|6|N|7|10|C|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|I|$]]

You cannot <code>DELETE</code> from multiple tables with a single expression in <code>SQL 2005</code> - or any other standard SQL for that matter. <code>Access</code> is the exception here.

The best method to get this effect is to specify <code>FOREIGN KEYS</code> between the table with an <code>ON</code> <code>DELETE</code> <code>trigger</code>.

blocks|key|1410570|text|这不能在一条语句中完成。您必须使用2个语句|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1410571|DELETE+FROM+TB1+WHERE+PersonID+=+'2';
DELETE+FROM+TB2+WHERE+PersonID+=+'2';|code-block|syntax|javascript|1410572|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This cannot be done in one statement. You will have to use 2 statements

<pre><code>DELETE FROM TB1 WHERE PersonID = '2';
DELETE FROM TB2 WHERE PersonID = '2';
</code></pre>

blocks|key|1195267|text|据我所知，你不能在一句话中做到这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1195268|但是您可以构建一个存储过程，在事务中的任何表中执行您想要的删除，这几乎是相同的。|1195269|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|1195295|text|我不认为你可以一次从多个表中删除(尽管我不确定)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1195296|然而，在我看来，你最好是通过级联删除的关系来实现这种效果。如果这样做，您将能够从一个表中删除记录，而另一个表中的记录将自动删除。|1195297|例如，假设这两个表表示一个客户和该客户的订单。如果将关系设置为级联删除，则只需删除customer表中的记录，订单将自动删除。|1195298|请参阅cascading+referential+integrity+constraints上的MSDN文档。|offset|length|1195299|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms186973(SQL.90).aspx)^0|0|0|0|3|17|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@$H|V|I|W|1|X]]|A|$]]|$1|J|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]]]

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 <a href="http://msdn.microsoft.com/en-us/library/ms186973(SQL.90).aspx" rel="nofollow noreferrer">cascading referential integrity constraints</a>.

blocks|key|1195335|text|为引用master的主键的details表指定外键，并设置Delete+rule+=+Cascade。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1195336|现在，当您从主表中删除一条记录时，基于删除行主键值的所有其他明细表记录将被自动删除。|1195337|因此，在这种情况下，主表的一次删除查询既可以删除主表数据，也可以删除子表数据。|1195338|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Specify foreign key for the details tables which references to the primary key of master and set 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.

blocks|key|1410691|text|您可以使用类似以下内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1410692|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++|code-block|syntax|javascript|1410693|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can use something like the following:

<pre><code>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 
</code></pre>

blocks|key|1410701|text|DELETE+TB1,+TB2
++++FROM+customer_details
++++++++LEFT+JOIN+customer_booking+on+TB1.cust_id+=+TB2.fk_cust_id
++++WHERE+TB1.cust_id+=+$id|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1410702|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>DELETE TB1, TB2
 FROM customer_details
 LEFT JOIN customer_booking on TB1.cust_id = TB2.fk_cust_id
 WHERE TB1.cust_id = $id
</code></pre>

blocks|key|1410712|text|CREATE+PROCEDURE+sp_deleteUserDetails
++++@Email+varchar(255)
AS
++++declare+@tempRegId+as+int
++++Delete+UserRegistration+where+Email=@Email++
++++set+@tempRegId+=+(select+Id+from+UserRegistration+where+Email+=+@Email)
++++Delete+UserProfile+where+RegID=@tempRegId

RETURN+0|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1410713|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>CREATE PROCEDURE sp_deleteUserDetails
 @Email varchar(255)
AS
 declare @tempRegId as int
 Delete UserRegistration where Email=@Email 
 set @tempRegId = (select Id from UserRegistration where Email = @Email)
 Delete UserProfile where RegID=@tempRegId

RETURN 0
</code></pre>

blocks|key|1195392|text|尝试此查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1195393|DELETE+TB1,+TB2+FROM+TB1+INNER+JOIN+TB2++
WHERE+TB1.PersonID+=+TB2.PersonID+and+TB1.PersonID+=+'2'|code-block|syntax|javascript|1195394|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this query

<pre><code>DELETE TB1, TB2 FROM TB1 INNER JOIN TB2 
WHERE TB1.PersonID = TB2.PersonID and TB1.PersonID = '2'
</code></pre>

blocks|key|1410756|text|在过程中使用此命令|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1410757|declare+cu+cursor+for+SELECT+[name]+FROM+sys.Tables+where+[name]+like+'tbl_%25'
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+'+%2B+@table
++++EXEC+sp_executesql+@sql
++++FETCH+NEXT+FROM+cu+INTO+@table+
END+++
CLOSE+cu;++
DEALLOCATE+cu;|code-block|syntax|javascript|1410758|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use this in procedure

<pre><code>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;
</code></pre>

This is the query I'm using:

<pre><code> DELETE TB1.*, TB2.*
 FROM TB1
 INNER JOIN TB2 ON TB1.PersonID = TB2.PersonID 
 WHERE (TB1.PersonID)='2'
</code></pre>

It's working fine in MS Access but getting error (Incorrect syntax near ','.) in SQL Server Express 2005. 

How to solve it? Please help.

Delete rows from multiple tables using a single query (SQL Express 2005) with a WHERE condition

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

这是我正在使用的查询： DELETE TB1.*, TB2.*   FROM TB1        INNER JOIN TB2 ON TB1.PersonID = TB2.PersonID   WHERE (TB1.PersonID)='2'它在MS Access中运行良好，但出现错误( '，‘附近的语法不正确。)在...

问使用带有WHERE条件的单个查询(SQL Express 2005)从多个表中删除行
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用带有WHERE条件的单个查询(SQL Express 2005)从多个表中删除行EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用带有WHERE条件的单个查询(SQL Express 2005)从多个表中删除行
EN