How to delete from a table where ID is in a list of IDs?

Question

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?

Aloysius

Answer 1

Your question almost spells the SQL for this:

DELETE FROM table WHERE id IN (1, 4, 6, 7)

Assault

@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. – Assault

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. – Kielce

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. – Skirret

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. – Gayomart

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 #68237675 – Jodiejodo

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? – Paction

Answer 2

delete from t
where id in (1, 4, 6, 7)

Nonattendance

Answer 3

        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) ========

Kal