In SQL, to delete a specific data record or row from a table we use the DELETE command. Make sure that when you use the DELETE clause you must mention the condition which helps the DELETE command to find the specific data row.
SQL DELETE Query Syntax
Follow this syntax to remove a row from the table.
DELETE FROM table_name WHERE [condition];
And if you want to delete multiple rows you can add multiple conditions by using the AND and OR operators. Example For the example considered this table of Students.
+------+--------+------+--------+-------+ | id | name | age | grades | marks | +------+--------+------+--------+-------+ | 1 | Luffy | 16 | A | 970 | | 2 | Naruto | 18 | A | 960 | | 3 | Zoro | 20 | A | 940 | | 4 | Sanji | 21 | B | 899 | | 5 | Nami | 17 | B | 896 | | 6 | Robin | NULL | B | 860 | +------+--------+------+--------+-------+
Query: Delete the record of Robin from the table.
DELETE FROM students WHERE name = "Robin";
Output
Query OK, 1 row affected (0.27 sec)
Verify To verify if Robin record has been deleted or not:
SELECT * FROM students;
Output
+------+--------+------+--------+-------+ | id | name | age | grades | marks | +------+--------+------+--------+-------+ | 1 | Luffy | 16 | A | 970 | | 2 | Naruto | 18 | A | 960 | | 3 | Zoro | 20 | A | 940 | | 4 | Sanji | 21 | B | 899 | | 5 | Nami | 17 | B | 896 | +------+--------+------+--------+-------+ 5 rows in set (0.00 sec)
Delete All record from the table
Using the DELETE clause we can also delete all the data from the table, this command comes very useful when we only want to delete all table data, instead of the complete table. Syntax Follow this syntax to delete the complete data from the table.
DELETE FROM table_name;
Note: Be careful when you use the delete the command and double-check your condition expression before executing the DELETE query, a small mistake can delete your complete table data.
Difference between DROP and DELETE
There is always a confusion between DROP and DELETE clause, both the commands can remove the data from your database. DROP command is used to delete the complete database or table. DELETE command is used to delete the data from the database, without deleting the table itself, if we delete the complete data using DELETE clause the table will be there with its fields.
Summary
- To remove a row or a record from the table we use the SQL DELETE command.
- Using the DELETE command we can also delete the complete data of the table.
- A DROP command can delete the complete table whereas the DELETE command can only delete the table complete data.
People are also reading: