MySQL Delete- Delete Records from Table

Once you insert records in a table you can always delete them. You may need to delete the records if they are not required or wrong records are inserted. MySQL Delete statement is a DML command to delete the unwanted records from table. The Delete statement permanently removes the records from given table. You can also delete rows of data depending upon some condition.  Let’s learn to use MySQL Delete statement to delete records from a table.  

Syntax MySQL DELETE Statement

DELETE FROM table-name

[WHERE condition]

  • DELETE, FROM – keywords of the MySQL delete statement
  • table-name is the name of the table from which you want to delete data
  • WHERE- keyword ( to define condition for deletion)
  • Condition- is the logical expression. All the data rows matching the stated condition will be applied

You can read more about MySQL Delete here

Examples – DELETE statement

Deleting All Rows

To delete all records from a table you don’t need to specify any column name. You have to only use the table name without WHERE condition.

DELETE FROM artists;

This statement will delete all rows from artists

Deleting Chosen Records

To delete only a few chosen records from a table you must give name of a table and the condition in the WHERE clause.  Here also no column names are to be given

DELETE from artists WHERE artist_id=2;

If a table uses a foreign key and has value from the primary key columns from another table, then the deletion will not happen.  A user can delete records from a table only if she has the DELETE rights on the table.

Points to remember

The following things must be considered to delete from a table

  • Deletion occurs on complete records not on specific columns of a table. MySQL Delete statement deletes rows not column data. To delete values from some specific columns, MySQL UPDATE statement must be used by setting the values of required columns as NULL or blank (‘ ‘) or zero.
  • To Deleting records in a table which have Primary Key defined as Foreign Key other table then the dependent records in the referencing table must be deleted first.
  • MySQL Delete Statement will delete all or few records from a table. To delete records on the basis of a condition, the condition must be given in WHERE Clause.