MySQL Delete

The DELETE command is one of the data manipulation command (DML) in MySQL. It is used to delete records from the MySQL Table. In this blog, we explain a simple, easy and efficient way to write DELETE command even you no need to remember the syntax.

DELETE Command Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

1. After DELETE Need to follow FROM clause with Table Name Ex: DELETE FROM Employee
2. Where clause: where the condition is useful for specifying which records need to DELETE. If you forgot WHERE condition then it will DELETE all records.
3. Even you can specify ORDER BY and LIMIT in the MySQL delete command. But if you are deleting all records don’t specify ORDER BY for best practice.
4. You can use LOW-PRIORITY, it will wait until other high priority threads that are using the same resource.
5. You can use QUICK for increasing PRIORITY.
6. If you use IGNORE then it will not return an error in case of Primary key or UNIQUE key violation

MySQL DELETE Query Example

DELETE e FROM  `employee` e
WHERE
  e.emp_no = 'E007';

SmartMySQL Workbench has advance options for writing DELETE command and you no need to remember DELETE command syntax.

If you Type D or DELETE key-word then drag table after D letter or DELETE Key-word then it generates DELETE COMMAND for that table. Ex: if you type D then drag the “Employee” table after D letter then it generates the following DELETE command.

DELETE e FROM `employee` e
WHERE
  e.emp_no = 'emp_no';

By default, it will generate WHERE condition with the primary key column. If you need other columns in WHERE clause then you need to drag the column after D letter or DELETE key-word.

For example, if I want an email column in the WHERE clause then I need to select an email column then drag it after the D keyword in the query browser.

DELETE e FROM employee e
 WHERE
   e.email = '';

If you drag new columns into the WHERE clause then those columns will be added in WHERE clause.

MySQL DELETE with JOIN

You can simply drag a table into the existing DELETE statement then it Reconstructs DELETE query with the table with JOIN condition. Ex: if I drag Department table then it generates the following DELETE command with JOIN condition.

DELETE e.* FROM employee e
  INNER JOIN departments d
    ON (d.dept_no = e.dep_no)
WHERE
  e.email = '';

You need to add d.* if you want to delete from both the tables. It is not possible to delete data from both tables using a single DELETE statement.

Ex: If I want to DELETE Employees who are working in the Marketing department and having more than 70 years old then in step 1 we need to drag birth_date column from the Employee table after D then we need to drag the Department table into the DELETE statement. It will generate the JOIN condition with the Department table. After we can add dept_name=’Marketing’ filter in the where clause and then also need to modify firth_date < curdate() – interval 70 years. we need to add e.* after DELETE.

DELETE e.* FROM employee e
  INNER JOIN departments d
    ON (d.dept_no = e.dep_no)
WHERE
  e.hire_date < CURDATE() - INTERVAL 70 YEAR 
and d.dept_name='Marketing';

Please let us know there are any other ideas to simply DELETE query construction, we will consider your ideas and give rewards for your ideas.

The SmartMySQL Workbench is free and you can download. If any problem contacts support@smartmysql.com.