[Syntax] DELETE

1. What is DELETE Statement?

DELETE is a Data Manipulation Language command, DML command and is used to remove tuples/records from a relation/table.

 

2. Syntax

DELETE FROM table 
 WHERE [conditions];

 

3. Error : You can't specify target table

When we use subquery in DELTE statement, there is error called : you can't specify target table. The reason why this error raised is because of feature of MySQL which can't use same table for update original table(We can use without error in Oracle and PostgreSQL).

 

To solve this error, we need to make arbitrary table in inner FROM statement like below code. 

 

DELETE FROM Person
 WHERE id NOT IN (SELECT id 
                  FROM (SELECT MIN(id) as id
                        FROM Person
                        GROUP BY email) as Person_s
                 );