一次删除多行 [英] Delete Multiple rows in once

问题描述

你好朋友，

Hello frnds,

CREATE PROCEDURE [dbo].[usp_DeleteDrafsMessages]
(
@DraftsDelID VARCHAR(500)
)
AS
BEGIN 
	IF (@DraftsDelID <> '')
	BEGIN 
		DELETE FROM tblMessage where MessageID= dbo.udfSplit(@DraftsDelID,',')
		
	END

END  

我想一次删除多个行，我的字符串中有ID号
@DraftsDelID，我将通过dbo.udfSplit(@DraftsDelID，''，'')获取每个ID，但是我上面的代码不可行，请帮我快速解决问题！

[Digimanus:添加了PRE标签]

I want to delete multiple number of rows in once ,i have number of id in my string
@DraftsDelID,i willl get each id by dbo.udfSplit(@DraftsDelID,'',''),but my above code is not feasible ,plz help me out fast !

[Digimanus: added PRE tags]

推荐答案

尝试以下方法:

Try something along these lines:

DECLARE @INSTR as VARCHAR(MAX)
SET @INSTR = '2,3,177,'
DECLARE @SEPERATOR as VARCHAR(1)
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
SET @SEPERATOR = ','
CREATE TABLE #tempTab (id int not null)
WHILE PATINDEX('%' + @SEPERATOR + '%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%' + @SEPERATOR + '%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO #tempTab (id) VALUES (@VALUE)
END
DELETE FROM myTable WHERE id IN (SELECT id FROM #tempTab)
DROP TABLE #tempTab

结构删除重复的事件或更多的事件是对事件进行编号.
您错过了一个真实的唯一字段，您应该在下面的示例中添加该字段.

The structure to delete double or more occurences is to number the occurences.
You miss a real unique field that you should add in the example I give below.

CREATE PROCEDURE [dbo].[usp_DeleteDrafsMessages]
(
@DraftsDelID VARCHAR(500)
)
AS
BEGIN 
	IF (@DraftsDelID <> '')
	BEGIN 
		Declare @MessageID varchar(max)
		set @MessageID = dbo.udfSplit(@DraftsDelID,',')
		SELECT Rowno,MessageID, UniqueID
		INTO #TEMP
		FROM
		(
			SELECT Row_Number() over (partition by MessageID order by MessageID) as Rowno, MessageID, UniqueID
			FROM tbl_Message
		) as t
		
		DELETE FROM tblMessage where UniqueID in (select UniqueID from #temp where Rowno > 1)
		DROP TABLE #temp
	END
 
END

你好朋友，
解决方案:-:)
创建过程[dbo].[usp_DeleteDrafsMessages]
(
@DraftsDelID VARCHAR(500)
)
AS
开始
IF(@DraftsDelID<>``'')
开始
从tblMessage中删除其中的MessageID(从dbo.udfSplit(@DraftsDelID，''，'')中选择ListMember)

END

结束
:)

Hello frnds,
Solution:-:)
CREATE PROCEDURE [dbo].[usp_DeleteDrafsMessages]
(
@DraftsDelID VARCHAR(500)
)
AS
BEGIN
IF (@DraftsDelID <> '''')
BEGIN
DELETE FROM tblMessage where MessageID in (select ListMember from dbo.udfSplit(@DraftsDelID,'',''))

END

END
:)

这篇关于一次删除多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！