How To Delete Data (Rows & Columns) From A Table in SQL

In this guide, you are going to know how to empty a table in SQL or delete all the data from a table with the help of the DELETE FROM table query.

So, the query you need to know to perform this task is:-

The SQL query to delete all data from a table

use query DELETE FROM table_name to erase all the data from a table.

DELETE FROM users;

But if you also want to reset auto increment then use this query

TRUNCATE TABLE table_name;

Delete a row

When you want to delete one row from a table then it can be done only by passing condition with the "WHERE" conditional clause.

Here, I am deleting the fourth row from the users table. Then the query will be~

DELETE FROM users WHERE mon_income="80000";

Or if there are multiple persons with the same mon_income then apply multiple conditions by using AND.

DELETE FROM users WHERE mon_income="20000" AND state="MP";

Delete multiple rows

DELETE FROM users LIMIT 1,2;

Delete a column

ALTER TABLE users DROP COLUMN mon_income;

Delete Multiple columns

ALTER TABLE users DROP gender, DROP state;

Delete first five rows

DELETE FROM users LIMIT 5;

Delete last five rows

I am assuming that you have a serial number (s_n) column (ex.- 1,2,3,4,........) in your table.

DELETE FROM users ORDER BY s_n DESC LIMIT 5;

Delete user with minimum monthly income

DELETE FROM users ORDER BY mon_income ASC LIMIT 1;

Delete a row (user) with maximum monthly income

DELETE FROM users ORDER BY mon_income DESC LIMIT 1;