if I have a list of IDs (1,4,6,7) and a db table where I want to delete all records where ID is in this list, what is the way to do that?
3 Answers
Your question almost spells the SQL for this:
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 ========= MAKE USE OF FUNCTION TO DO A DELETE OPERATION THIS WAY ==========
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 , ',')); ========= HOPE THIS HELPS (smiles) ========
