30
loading...
How To Delete First Or Last n Rows In MySQL
You can use this example database schema for this question or otherwise, you may use your database table for testing.
CREATE TABLE `students` (
`id` int UNSIGNED NOT NULL,
`stid` varchar(16) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE `students` ADD PRIMARY KEY (`id`);This SQL query will delete every record which has a primary key
id less than or equal to 100.DELETE FROM `students` WHERE `id` <= 100;This SQL query will delete every record which has an
id greater than 900.DELETE FROM `students` WHERE `id` > 900;The above queries will delete records considering the value of
id of the table. The problem is, what happens when the id of the table was not incremented exactly one by one. Or some of the records have been already removed. Or even the id is not numerical. Under that kind of situation, the above-mentioned queries will not work as expected.This query will sort the entire table by
created_at column and delete the first 100 records.DELETE FROM `students` ORDER BY `created_at` ASC limit 100We can use
DESC (descending) instead of ASC (ascending) to invert the order of the above query, so it will delete the last 100 records.DELETE FROM `students` ORDER BY `created_at` DESC limit 100Feel free to visit devtonight.com for more related content.
30