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 | Singapore | $15000 |
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 |
|
|
|
|
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 |
|
|
|
|
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.