11 - MySQL Delete

Delete in MySQL refers to deleting one or more records from the MySQL table. Delete in MySQL is achieved by DELETE FROM command.  The basic syntax of DELETE command is:

DELETE FROM TABLE_NAME [CONDTIONS];

MySQL provides following by the DELETE command:

  • Delete all rows of the table at once OR truncate the table.
  • Delete particular rows based on some conditions.
  • Delete from a table using JOIN conditions to match records in another table

Delete shouldn’t be run without the WHERE clause as the data can’t be rollback once deleted.  The “i-am-dummy-flag” option as described in update chapter also applies in case of DELETE command.

Deleting all rows of the Table

We can delete all rows of the table at once but this is risky because we can’t undo this operation as it is AUTO COMMOT operation. To achieve this we have two options:

  • Give always truth condition in WHERE clause
  • Don’t give any condition means omit the WHERE clause from the DELETE command.

For example:

We have a data of the company names along with their address and price of some server.

ID

Company

Address

Amount

1

HCL

NY

$1000

2

Apple

Chicago

$2000

3

IBM

INDIA

$3000

4

Google

Singapore

$15000

Now the DELETE command for remove all the rows are written as:

DELETE FROM company WHERE 1=1;

Here company is the table name and WHERE condition is always true that is the first case as defined above.

DELETE FROM company;

This works same as above DELETE command and can be run only when “i-am-a-dummy-flag” option is disabled. The output of these DELETE command is the blank table as:

ID

Company

Address

Amount

 

 

 

 

Deleting Particular rows based on some Conditions

To delete some rows or tuples from the given table company based on some conditionsdefined in the WHERE clause.

For example, If we want to Delete the record of Google then the DELETE query will be:

DELETE FROM TABLE company where Company=’Google’  ;

After deleting from the table, the data becomes:

ID

Company

Address

Amount

1

HCL

NY

$4000

2

Apple

Chicago

$4000

3

IBM

Florida

$4000

 

 

 

 

The last row containing the Google record is deleted. This delete command deleted all the rows that are matched up with the conditions defined in the WHERE clause. The result shows how many rows are affected and in how much time as:

mysql> DELETE FROM company WHERE Amount=’$4000’

Query OK, 3 rows affected (0.09 sec)

We can use multiple conditions in the WHERE clause separated by any operator like AND / OR / between / Greater then / Less then etc.

Delete Rows from a tables using JOIN

When we want to delete the data of a table using conditions from the other table based on some JOIN conditions upon a common column then we need to use DELETE command with USING operator. For example:

When we need to Delete the rows from table A whose Address isParis in Table B then we have to use the JOIN on the basis of common column ID as:

DELETE FROM TABLE A USING TABLE A INNER JOIN B on A.Id = B.Id WHERE B.Address=’Paris’;

After the above DELETE command, data will be:

Here Table A row number 3 is deleted that is matching with the Address Paris in Table B.

Like us on Facebook