SQL DELETE Query

By | September 8, 2020
SQL DELETE Query

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.

Vamware
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.

Leave a Reply

Your email address will not be published. Required fields are marked *