Delete

To delete (remove) data from a table, the DELETE statement is used. DELETEcan be used in two ways:

1. To delete specific rows from a table

2. To delete all rows from a table

DELETE FROM table_name
WHERE condition;

DELETE FROM Customers
WHERE cust_id = "1000000006";

If the WHERE clause were omitted, this statement would have deleted every customer in the table!

The DELETE statement deletes rows from tables, even all rows from tables. But DELETE never deletes the table itself.

Multi-Table Deletes

For example, to delete rows from bothT1 andT2 tables that meet a specified condition, you use the following statement:

DELETE T1, T2
FROM T1 INNER JOIN T2 ON T1.key = T2.key
WHERE condition;

• Notice that you put table namesT1andT2between the DELETEandFROM keywords. If you omitT1table, theDELETEstatement only deletes rows in T2table. Similarly, if you omitT2table, theDELETEstatement will delete only rows in T1table.

• The expressionT1.key = T2.keyspecifies the condition for matching rows betweenT1andT2tables that will be deleted.

• The condition in the WHEREclause determine rows in theT1andT2that will be deleted.

In the following code, we INNER JOINs three tables.

DELETE t1, t2 
FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

DELETE FROM t1, t2 
USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

These two syntax return the same results. These statements use all three tables when searching for rows to delete, but delete matching rows only from tablest1andt2.

For the first multiple-table syntax, only matching rows from the tables listed before the FROMclause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROMclause (before theUSINGclause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching.