Delete more than one rows from a table using id in MySQL?

You can use IN statement to delete more than one rows from a table using id in MySQL. The syntax is as follows −

delete from yourTableName where yourColumnName in(value1,value2,.....valueN);

To understand the above syntax, let us create a table. The following is the query to create a table.

mysql> create table DeleteManyRows    −> (    −> Id int,    −> Name varchar(200),    −> Age int    −> ); Query OK, 0 rows affected (3.35 sec)

Insert some records in the table with the help of insert command. The query is as follows −

mysql> insert into DeleteManyRows values(1,'John',23); Query OK, 1 row affected (0.66 sec) mysql> insert into DeleteManyRows values(2,'Johnson',22); Query OK, 1 row affected (0.48 sec) mysql> insert into DeleteManyRows values(3,'Sam',20); Query OK, 1 row affected (0.39 sec) mysql> insert into DeleteManyRows values(4,'David',26); Query OK, 1 row affected (0.35 sec) mysql> insert into DeleteManyRows values(5,'Carol',21); Query OK, 1 row affected (0.10 sec) mysql> insert into DeleteManyRows values(6,'Smith',29); Query OK, 1 row affected (0.14 sec)

Display all records from the table with the help of select statement. The query is as follows −

mysql> select *from DeleteManyRows;

The following is the output −

+------+---------+------+ | Id   | Name    | Age | +------+---------+------+ | 1    | John    | 23   | | 2    | Johnson | 22   | | 3    | Sam     | 20   | | 4    | David   | 26   | | 5    | Carol   | 21   | | 6    | Smith   | 29 | +------+---------+------+ 6 rows in set (0.00 sec)

Here is the query to delete rows from the table with the help of IN statement. The query is as follows −

mysql> delete from DeleteManyRows where Id in(1,2,3,4); Query OK, 4 rows affected (0.25 sec)

Let us check how many rows are now present after deleting multiple rows like 1,2,3,4. The query is as follows −

mysql> select *from DeleteManyRows;

The following is the output −

+------+-------+------+ | Id   | Name  | Age | +------+-------+------+ | 5 | Carol | 21 | | 6 | Smith | 29 | +------+-------+------+ 2 rows in set (0.00 sec)