Tutorial by Examples

DELETE FROM `table_name` WHERE `field_one` = 'value_one' This will delete all rows from the table where the contents of the field_one for that row match 'value_one' The WHERE clause works in the same way as a select, so things like >, <, <> or LIKE can be used. Notice: It is necessa...

DELETE FROM table_name ; This will delete everything, all rows from the table. It is the most basic example of the syntax. It also shows that DELETE statements should really be used with extra care as they may empty a table, if the WHERE clause is omitted.

DELETE FROM `table_name` WHERE `field_one` = 'value_one' LIMIT 1 This works in the same way as the 'Delete with Where clause' example, but it will stop the deletion once the limited number of rows have been removed. If you are limiting rows for deletion like this, be aware that it will delete th...

MySQL's DELETE statement can use the JOIN construct, allowing also to specify which tables to delete from. This is useful to avoid nested queries. Given the schema: create table people ( id int primary key, name varchar(100) not null, gender char(1) not null ); insert people (id,na...

DELETE FROM `myTable` WHERE `someColumn` = 'something' The WHERE clause is optional but without it all rows are deleted.

TRUNCATE tableName; This will delete all the data and reset AUTO_INCREMENT index. It's much faster than DELETE FROM tableName on a huge dataset. It can be very useful during development/testing. When you truncate a table SQL server doesn't delete the data, it drops the table and recreates it, th...

MySQL allows to specify from which table the matching rows must be deleted -- remove only the employees DELETE e FROM Employees e JOIN Department d ON e.department_id = d.department_id WHERE d.name = 'Sales' -- remove employees and department DELETE e, d FROM Emp...