MySQL Delete Best Pratice

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.

MySQL Delete all Rows Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]

1. After DELETE Need to follow FROM clause with Table Name Ex: DELETE FROM Employee.
2. You can use LOW-PRIORITY , it will wait until other high priority threads that are using the same resource.
3. You can use QUICK for increasing PRIORITY.
4. If you use IGNORE then it will not return an error in case of Primary key or UNIQUE key violation.

MySQL Delete all Rows Example

DELETE  e FROM  `employee` e;

For removing all table data need to omit the where clause. In MySQL, there is another way to remove all table data by using the TRUNCATE command.

TRUNCATE TABLE `employee` e;

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

If you Type U 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 the 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 keyword.

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 d.* if you want to delete from the Department table. 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 the 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 best tool to generate DETE command include JOIN. It is free and you can download. If any problem contacts support@smartmysql.com.