how to use "in" to delete multiple records (by using parameter)-CodePudding

I'm trying to delete multiple records in one statement I use the following Sql:

DELETE FROM `tblregs2` WHERE `RegdID` IN (@RegdID)

and I collect the parameter value during execute the codes and save the values inside a string variable.

but the result that I get is: "1,2,3,4" so the SQL statement will be:

DELETE FROM `tblregs2` WHERE `RegdID` IN ("1,2,3,4")

but "in" doesn't accept the double quotation so I need to use it like 1,2,3,4

    DELETE FROM `tblregs2` WHERE `RegdID` IN (1,2,3,4)

how to get a result like that?

by the way, the code is working with "FIND_IN_SET" but I'm working with two database types (MySQL SQLite) so "In" works with both of them.

CodePudding user response：

I can't believe I am saying this but don't use parameters. If you are parsing each number for you string you should avoid Sql injection.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim s = "1,2,3,4"
    Dim strSQL = $"DELETE FROM `tblregs2` WHERE `RegdID` IN ({s})"
    MessageBox.Show(strSQL)
End Sub