FROM<\/code> keywords, followed by the table name, as in the following example:<\/p>\nDELETE FROM airplanes;<\/pre>\nThe statement will delete all data from the airplanes<\/code> table, unless safe mode is enabled. Safe mode is typically enabled by default on a MySQL instance to help limit the possibility of updating or deleting all data in a table.<\/p>\nMuch the same as described for the UPDATE statement, if safe mode is enabled on your MySQL instance, you\u2019ll receive the following error message when you try to run the above statement:<\/p>\n
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.<\/code><\/p>\nWhen safe mode is enabled, you cannot run a DELETE<\/code> statement without a WHERE<\/code> clause that does not specify a key column in the WHERE clause criteria, unless you include a LIMIT<\/code> clause. This helps to ensure that you don\u2019t inadvertently delete a table full of data. You can disable safe mode (as the error message suggests) by setting the server properties or by temporarily disabling safe mode when you run the DELETE<\/code> statement. The temporary approach is usually the safest.<\/p>\nTo temporarily disable safe mode, use a SET<\/code> statement to change the SQL_SAFE_UPDATES<\/code> system variable to 0<\/code> prior to running your DELETE<\/code> statement and then set the variable to 1<\/code> after running the statement, as shown in the following example:<\/p>\nSET SQL_SAFE_UPDATES = 0;\r\n\r\nDELETE FROM airplanes;\r\n\r\nSET SQL_SAFE_UPDATES = 1;<\/pre>\nThe SET<\/code> statements toggle the system variable off and then on during the current session. When taking this approach, be aware that if your DELETE<\/code> statement generates an error, statement execution will stop and the second SET<\/code> statement will not execute, so make sure you run this statement to reset the SQL_SAFE_UPDATES<\/code> variable to 1<\/code>. Also note that the SET<\/code> statement supports the optional GLOBAL<\/code> modifier, which defines a variable at the global scope. However, I recommend that you do not<\/em> use this option when disabling safe deletes. It is less risky to disable safe mode at the session level to avoid any inadvertent data modifications. Use GLOBAL<\/code> only if it\u2019s essential in your situation.<\/p>\nOnce safe mode is disabled, you should be able to run your DELETE<\/code> statement without generating an error. After you do, you can confirm your changes with a simple SELECT<\/code> statement that retrieves all data from the airplanes<\/code> table. The statement should return no rows.<\/p>\nAdding a WHERE<\/strong><\/code> clause to your DELETE<\/strong><\/code> statement<\/h2>\nIn most cases, you\u2019ll want to include a WHERE<\/code> clause in your DELETE<\/code> statements so you can target which rows in a table should be deleted (as opposed to deleting all rows). The WHERE<\/code> clause defines one or more search conditions that specify exactly what data to delete. To see how this works, you should first add the data back to the airplanes<\/code> table (assuming you\u2019re trying out these examples):<\/p>\nINSERT INTO airplanes \r\n (plane, manufacturer_id, engine_type, engine_count, \r\n wingspan, plane_length, max_weight, icao_code)\r\nVALUES\r\n ('Learjet 24',1001,'Jet',2,35.58,43.25,13000,'LJ24'),\r\n ('Learjet 24A',1001,'Jet',2,35.58,43.25,12499,'LJ24'),\r\n ('Challenger (BD-100-1A10) 300'\r\n ,1001,'Jet',2,63.83,68.75,38850,'CL30'),\r\n ('Challenger (BD-100-1A10) 350'\r\n ,1001,'Jet',2,69,68.75,40600,'CL30'),\r\n ('Challenger (CL-600-1A11) 600'\r\n ,1001,'Jet',2,64.33,68.42,36000,'CL60'),\r\n ('Challenger (CL-600-2A12) 601'\r\n ,1001,'Jet',2,64.33,68.42,42100,'CL60'),\r\n ('A.109 Airedale',1002,'piston',1,36.33,26.33,2750,'AIRD'),\r\n ('A.61 Terrier',1002,'piston',1,36,23.25,2400,'AUS6'),\r\n ('B.121 Pup',1002,'piston',1,31,23.17,1600,'PUP'),\r\n ('B.206',1002,'piston',2,55,33.67,7500,'BASS'),\r\n ('D.4-108',1002,'piston',1,36,23.33,1900,'D4'),\r\n ('D.5-108 Husky',1002,'piston',1,36,23.17,2400,'D5');\r\n\r\nSELECT *\r\nFROM airplanes;<\/pre>\nConfirm that the data has been re-added to the airplanes<\/code> table by looking at the output of the SELECT<\/code> statement. The table should now be populated with the same 12 rows, with one notable difference. The first plane_id<\/code> value is now 113<\/code> rather than 101<\/code> because MySQL tracks the last auto-incremented value that was assigned to a row, even if that row has been deleted.<\/p>\nAfter you\u2019ve inserted the data into the airplanes<\/code> table, you can run the following DELETE<\/code> statement, which includes a basic WHERE<\/code> clause:<\/p>\nDELETE FROM airplanes\r\nWHERE icao_code = 'pup';<\/pre>\nThe search condition specifies that the icao_code<\/code> value must equal pup<\/code> for a row to be deleted. However, if you try to run this statement and safe mode is enabled, MySQL will again return error 1175:<\/p>\nError Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.<\/code><\/p>\nMySQL returns this error because the WHERE<\/code> clause does not include a key column in its search condition. To get around this, you can again temporarily disable safe mode at the session level:<\/p>\nSET SQL_SAFE_UPDATES = 0;\r\n\r\nDELETE FROM airplanes\r\nWHERE icao_code = 'pup';\r\n\r\nSET SQL_SAFE_UPDATES = 1;<\/pre>\nIf you query the airplanes<\/code> table after running these statements, the table should now include only 11 rows rather than 12. Only one row satisfied the search condition, so only that row was deleted.<\/p>\nIn cases where you specifically know what rows to delete, you should try to use a key column in your search condition to avoid having to disable safe mode. For example, the row deleted in the previous example had a plane_id<\/code> value of 121<\/code>. As a result, you could have recast the DELETE<\/code> statement as follows:<\/p>\nDELETE FROM airplanes\r\nWHERE plane_id = 121;<\/pre>\nOf course, it\u2019s not always practical to use a key column, in which case, you should define the WHERE<\/code> clause in a way that best suits your situation, even if it means specifying multiple search conditions, as in the following example:<\/p>\nSET SQL_SAFE_UPDATES = 0;\r\n\r\nDELETE FROM airplanes\r\nWHERE engine_type = 'piston' AND max_weight < 2500 ;\r\n\r\nSET SQL_SAFE_UPDATES = 1;<\/pre>\nBecause the WHERE<\/code> clause contains no key column, safe mode must again be disabled during the session.<\/p>\nThe WHERE<\/code> clause itself includes two search conditions. The first one specifies that the engine_type<\/code> value must be piston<\/code>, and the second one specifies that the max_weight<\/code> value must be less that 2500<\/code>. The search conditions are connected by the AND<\/code> logical operator, which means that both conditions must evaluate to true for a row to be deleted.<\/p>\nIn this case, several rows matched both search conditions, so they were all removed from the airplanes<\/code> table. If you query the table, you should find that it now includes only eight rows.<\/p>\nAdding ORDER<\/strong><\/code> BY<\/strong><\/code> and LIMIT<\/strong><\/code> clauses to your DELETE<\/strong><\/code> statement<\/h2>\nTogether, the ORDER<\/code> BY<\/code> and LIMIT<\/code> clauses help you better control how rows are deleted from a table. To see how this works, start be adding a row back into the airplanes<\/code> table:<\/p>\nINSERT INTO airplanes \r\n (plane, manufacturer_id, engine_type, engine_count, \r\n wingspan, plane_length, max_weight, icao_code)\r\nVALUES\r\n ('D.4-108',1002,'piston',1,36,23.33,1900,'D4');<\/pre>\nBecause this row is being added separately from when the previous rows were added, it will have a different create_date<\/code> value from the other rows. (The value is a timestamp.) You can use this value to single out the row when building your DELETE<\/code> statement:<\/p>\nDELETE FROM airplanes\r\nORDER BY create_date DESC\r\nLIMIT 1;<\/pre>\nNotice that the statement includes no WHERE<\/code> clause with a key column and that there are no SET<\/code> statements. You can get away with this here because the DELETE<\/code> statement includes a LIMIT<\/code> clause. If necessary, you can include a WHERE<\/code> clause with the ORDER<\/code> BY<\/code> and LIMIT<\/code> clauses, but it\u2019s not needed in this case.<\/p>\nThe ORDER<\/code> BY<\/code> clause specifies that the rows should be deleted based on the create_date<\/code> values, sorted in descending order. This ensures that the last row inserted is the first row deleted, assuming it has a unique create_date<\/code> value. The LIMIT<\/code> clause then specifies that only one row should be deleted. This will be the first row as it is determined by the ORDER<\/code> BY<\/code> clause. You might also consider this approach when deleting archived data, except that you would likely specify an amount other than 1 in the LIMIT<\/code> clause, such as 100, 1000, or another value.<\/p>\nIt’s hard to say how often you\u2019ll use the ORDER<\/code> BY<\/code> and LIMIT<\/code> clauses in your queries. But it\u2019s good to know that you have this option if you want to apply similar logic when deleting data from your database tables.<\/p>\nYou can also use these clauses independently of each other. For example, you might use the LIMIT<\/code> clause when you need to delete a large number of rows from a table and you\u2019re concerned about the impact on database performance. Instead of deleting all the rows at once, you can delete them in batches based on the number of rows specified in the LIMIT<\/code> clause. Then you can simply rerun the DELETE<\/code> statement until all the target rows have been removed.<\/p>\nUsing the IGNORE<\/strong><\/code> modifier in your DELETE<\/strong><\/code> statement<\/h2>\nAs noted earlier, the DELETE<\/code> statement supports the use of the optional IGNORE<\/code> modifier, which you also saw in the INSERT<\/code> and UPDATE<\/code> statements. When IGNORE<\/code> is used, your DELETE<\/code> statement will return a warning rather than an error if an issue arises. In addition, MySQL will continue with the statement execution. If you don\u2019t use IGNORE<\/code>, MySQL will return an error and stop all statement execution, including any statements in the batch that follow the DELETE<\/code> statement.<\/p>\nTo see how this works, start by running the following INSERT<\/code> statement, which adds several rows to the manufacturers<\/code> table:<\/p>\nINSERT INTO manufacturers (manufacturer)\r\nVALUES ('Airbus'), ('Beechcraft'), ('Cessna'), ('Piper');\r\n\r\nSELECT *\r\nFROM manufacturers;<\/pre>\nFrom the output of this batch, you should find that the Airbus row has a manufacturer_id<\/code> value of 1003<\/code>. You will use this value as the foreign key value when you add a row to the airplanes<\/code> table for an Airbus plane. To add the row, run the following INSERT<\/code> statement:<\/p>\nINSERT INTO airplanes \r\n (plane, manufacturer_id, engine_type, engine_count, \r\n wingspan, plane_length, max_weight, icao_code)\r\nVALUES\r\n('A220-100',1003,'Jet',2,115.08,114.75,134000,'BCS1');<\/pre>\nSuppose you now want to delete all the rows you recently added to the manufacturers<\/code> table. You might try to run the following DELETE<\/code> statement, using the manufacturer_id<\/code> values for those rows:<\/p>\nDELETE FROM manufacturers\r\nWHERE manufacturer_id IN (1003, 1004, 1005, 1006);<\/pre>\nWhen you try to run this statement, MySQL stops statement execution and returns the following error, which indicates you have a foreign key violation:<\/p>\n
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`travel`.`airplanes`, CONSTRAINT `fk_manufacturer_id` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`manufacturer_id`))<\/code><\/p>\nMySQL returned this error because you tried to delete a row that was being referenced by the airplanes<\/code> table. As a result, the entire statement failed and no rows were deleted. However, you can ensure that the DELETE<\/code> statement continues to execute even if one of the deletions fails by including the IGNORE<\/code> modifier:<\/p>\nDELETE IGNORE FROM manufacturers\r\nWHERE manufacturer_id IN (1003, 1004, 1005, 1006);<\/pre>\nNow the statement returns the following message, which shows the number of rows that have been affected and provides a warning:<\/p>\n
3 row(s) affected, 1 warning(s): 1451 Cannot delete or update a parent row: a foreign key constraint fails (`travel`.`airplanes`, CONSTRAINT `fk_manufacturer_id` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`manufacturer_id`))<\/code><\/p>\nFrom this message, you can see that three rows have been deleted and that there has been a foreign key violation. If you query the manufacturers<\/code> table, you\u2019ll find that it now contains only three rows, including the one for Airbus. To remove all the Airbus data, you must first delete any referencing records from the airplanes<\/code> table:<\/p>\nDELETE FROM airplanes\r\nWHERE manufacturer_id = 1003;<\/pre>\nYou should then be able to run the following DELETE<\/code> statement to remove the Airbus record from the manufacturers<\/code> table, which will leave you with only two rows in that table:<\/p>\n