如何删除具有不同 ID 的多行?
我想做这样的事情:
DELETE FROM table WHERE id IN (SELECT ....)
我该怎么做?
I want to do something like this:
DELETE FROM table WHERE id IN (SELECT ....)
How can I do that?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
如果您必须选择 ID:
如果您已经知道它们(并且数量不在数千):
If you have to select the id:
If you already know them (and they are not in the thousands):
免责声明:根据具体情况,以下建议可能会产生开销。该功能仅在 MSSQL 2008 R2 上进行了测试,但接缝与其他版本兼容
如果您想对许多 Id 执行此操作,您可以使用 function 它创建一个临时表,您可以在其中从选择中删除
查询的样子:
这是该函数:
Disclaim: the following suggestion could be an overhead depending on the situation. The function is only tested with MSSQL 2008 R2 but seams be compatible to other versions
if you wane do this with many Id's you may could use a function which creates a temp table where you will be able to DELETE FROM the selection
how the query could look like:
here is the function:
你可以做这个。
创建过程[dbo]。[sp_DELETE_MULTI_ROW]
@代码XML
,@ERRFLAG CHAR(1) = '0' OUTPUT
为
NOCOUNT ON
设置事务隔离级别 读取未提交
删除 tb_SampleTest
在哪里
代码输入(
SELECT Item.value('.', 'VARCHAR(20)')
FROM @CODE.nodes('RecordList/ID') AS x(Item)
)
如果 @@ROWCOUNT = 0
SET @ERRFLAG = 200
SET NOCOUNT OFF
You can make this.
CREATE PROC [dbo].[sp_DELETE_MULTI_ROW]
@CODE XML
,@ERRFLAG CHAR(1) = '0' OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DELETE tb_SampleTest
WHERE
CODE IN(
SELECT Item.value('.', 'VARCHAR(20)')
FROM @CODE.nodes('RecordList/ID') AS x(Item)
)
IF @@ROWCOUNT = 0
SET @ERRFLAG = 200
SET NOCOUNT OFF