Delete alternate rows from a MySQL table

To delete alternate rows from MYSQL table, suppose we have the following employee records -

CREATE TABLE IF NOT EXISTS `empdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(25) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO `empdata` (`id`, `name`, `email`, `phone`) VALUES
(1, 'Anjali', This email address is being protected from spambots. You need JavaScript enabled to view it.', 878433948),
(2, 'Priska', This email address is being protected from spambots. You need JavaScript enabled to view it.', 493905490),
(3, 'Abhi', This email address is being protected from spambots. You need JavaScript enabled to view it.', 403022139),
(4, 'Joya', This email address is being protected from spambots. You need JavaScript enabled to view it.', 342345329),
(5, 'Ammy', This email address is being protected from spambots. You need JavaScript enabled to view it.', 239848342),
(6, 'Lussi', This email address is being protected from spambots. You need JavaScript enabled to view it.', 490290331);

These are the methods that you can use to fetch and delete alternate or ODD-EVEN records from a MySQL table -

Method1 : MySQL MOD() method

MySQL MOD() method returns the remainder of a number divided by another number. So for getting alternate rows, we can divide the ID with 2 and fetch only those having remainder 1 and delete them.

DELETE FROM empdata 
WHERE id IN(
    SELECT * 
    FROM (
    SELECT id FROM empdata GROUP BY id having mod(id,2)=1
    )temp
);
SELECT * FROM `empdata`;

Output of the above statement

+----+--------+--------------------+-----------+
| id | name   | email              | phone     |
+----+--------+--------------------+-----------+
|  4 | Joya   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 342345329 |
|  2 | Priska | This email address is being protected from spambots. You need JavaScript enabled to view it. | 493905490 |
|  6 | Lussi  | This email address is being protected from spambots. You need JavaScript enabled to view it.  | 490290331 |
+----+--------+--------------------+-----------+

The above statement deletes only ODD rows. If you want to delete even rows, write the statement as-

DELETE FROM empdata 
WHERE id IN(
    SELECT * 
    FROM (
    SELECT id FROM empdata GROUP BY id having mod(id,2)=0
 )temp
);
SELECT * FROM `empdata`;

Output of the above statement

+----+--------+--------------------+-----------+
| id | name   | email              | phone     |
+----+--------+--------------------+-----------+
|  5 | Ammy   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 239848342 |
|  3 | Abhi   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 403022139 |
|  1 | Anjali | This email address is being protected from spambots. You need JavaScript enabled to view it. | 878433948 |
+----+--------+--------------------+-----------+

Method 2

We can also use the modulus operator instead of mod() method, like- the given statement fetch only even rows and delete them -

DELETE FROM empdata 
WHERE id%2 = 0;
SELECT * FROM `empdata`;

Output of the above statement

+----+--------+--------------------+-----------+
| id | name   | email              | phone     |
+----+--------+--------------------+-----------+
|  5 | Ammy   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 239848342 |
|  1 | Anjali | This email address is being protected from spambots. You need JavaScript enabled to view it. | 878433948 |
|  3 | Abhi   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 403022139 |
+----+--------+--------------------+-----------+

similarly, the following statement fetch and delete only odd rows-

DELETE FROM empdata 
WHERE id%2 = 1;
SELECT * FROM `empdata`;

Output of the above statement

+----+--------+--------------------+-----------+
| id | name   | email              | phone     |
+----+--------+--------------------+-----------+
|  6 | Lussi  | This email address is being protected from spambots. You need JavaScript enabled to view it.  | 490290331 |
|  4 | Joya   | This email address is being protected from spambots. You need JavaScript enabled to view it.   | 342345329 |
|  2 | Priska | This email address is being protected from spambots. You need JavaScript enabled to view it. | 493905490 |
+----+--------+--------------------+-----------+