如何从 ID 位于 ID 列表中的表中删除？

如果我有一个 ID 列表(1,4,6,7)和一个数据库表，在这个表中我想删除 ID 在这个列表中的所有记录，那么做到这一点的方法是什么？

176235 次浏览
小开
最佳答案

你的问题几乎拼出了这个问题的 SQL 语句:

DELETE FROM table WHERE id IN (1, 4, 6, 7)
小开 
delete from t
where id in (1, 4, 6, 7)
小开 
        CREATE FUNCTION dbo.func_SplitString
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT;


SET @StartIndex = 1;


--ADD THE SEPERATING CHARACTER AT THE END OF THE STRING FOR LOOP PURPOSE


IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character;
END


-- LOOP AS LONG AS THE SEPARATOR EXISTS IN THE STRING


WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
-- GET INDEX OF THE SEPARATOR (THIS INDICATES AN ITEM WE CAN TAKE OFF THE STRING)


SET @EndIndex = CHARINDEX(@Character, @Input);


-- INSERT ITEM INTO TEMP TABLE


INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1);
    

-- REMOVE ITEM FROM ORIGINAL STRING ALONG WITH THE SEPARATOR WE HAVE JUST WORKED WITH


-- THIS REMOVES THE ITEM AFTER ADDING IT TO THE TEMP TABLE ALONG WITH THE SEPARATOR FOR THE ITEM
-- UNTIL THERE IS NO SEPARATOR ANYMORE IN THE ORIGINAL STRING


SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input));
END


RETURN
END
GO

= = = = = = = = = = = = 使用函数这样做一个删除操作 = = = = = = = = = = 

     DECLARE @ListOfIDs varchar(100);


SET @ListOfIDs = '100,200,300,400,500';


DELETE FROM [dbo].[tableContainingDataToDelete]
WHERE
ID IN(SELECT CAST(Item AS int)
FROM dbo.func_SplitString(@ListOfIDs , ','));

= = = = = = = = = = = 希望这个帮助(微笑) = = = = = = = = = = = = =


提交答案