122

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?

TylerH
  • 20,799
  • 66
  • 75
  • 101
leora
  • 188,729
  • 360
  • 878
  • 1,366

3 Answers3

239

Your question almost spells the SQL for this:

DELETE FROM table WHERE id IN (1, 4, 6, 7)
Matti Virkkunen
  • 63,558
  • 9
  • 127
  • 159
  • @jayarjo: Any difference would probably be quite negligible, and I don't think there is any reason one-by-one delete would be any more efficient anyways. – Matti Virkkunen Jun 08 '10 at 16:50
  • It's just where performance may hit convenience. For example I already got functions to do it one by one in place. But if there were a chance of rising performance I could write some additional code, otherwise probably it won't worth it. – jayarjo Jun 09 '10 at 10:55
  • 9
    One-by-one would almost certainly be slower, certainly on Oracle or PostgreSQL. Breaking out SQL operations into many smaller operations is a great way of getting bad performance. – David Aldridge Dec 19 '12 at 12:12
  • 1
    I found that 10,000 items in the In clause took about 200 seconds, but 1000 took 3 seconds (or 30 seconds in normalized comparison). Size does matter. – ohmusama Feb 12 '15 at 02:06
  • Also it is good to know that if you assign variable with select @SOMEVAAR := GRUP_CONCAT(id) --- IN (@SOMEVAAR) will not work as expected and there are no index used, and it removes only first id from the string list. See this example https://stackoverflow.com/questions/68237175/deleting-records-in-mysql-where-id-in-variable-2-3-4 – DevWL Jul 04 '21 at 20:13
  • Is there any limit on how many arguments can be in "IN"? What if I dynamically allocated the list that is to be used in "IN" statement. And it can reach to 10k, 20k? – Pomme Aug 09 '21 at 15:29
18
delete from t
where id in (1, 4, 6, 7)
Carl Manaster
  • 39,912
  • 17
  • 102
  • 155
-5
        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) ========

Bernard Oreva
  • 124
  • 1
  • 4