MySQL Delete From Select

MySQL delete from select used to delete returned records of select query.

MySQL Delete With Select Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
 [PARTITION (partition_name [, partition_name] ...)]
    [WHERE column_name=(select column_name from tbl_name)]

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.

MySQL Delete With Select Example

DELETE fa FROM `sakila`.`film_actor` fa
WHERE
      fa.film_id in (SELECT
  film_id
FROM
  film f
WHERE
  f.film_id = '');

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 add 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 is free and you can download. If any problem contacts support@smartmysql.com.