{"id":97941,"date":"2020-12-14T11:00:00","date_gmt":"2020-12-14T09:00:00","guid":{"rendered":"https:\/\/examples.javacodegeeks.com\/?p=97941"},"modified":"2021-11-07T23:37:35","modified_gmt":"2021-11-07T21:37:35","slug":"sql-delete-query-example","status":"publish","type":"post","link":"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/","title":{"rendered":"SQL Delete Query Example"},"content":{"rendered":"<h2 class=\"wp-block-heading\" id=\"h-1-introduction\">1. Introduction<\/h2>\n<p>In this article, we will look at one of the Data Manipulation Language (DML) constructs or statements called SQL Delete query using the MySQL RDBMS.<\/p>\n<h2 class=\"wp-block-heading\" id=\"h-2-what-is-data-manipulation-language\">2. What is Data Manipulation Language?<\/h2>\n<p>In SQL, Data Manipulation language consists of SQL data-change statements that modify data but not the underlying schema or database objects. The commands included in the data manipulation language are:<\/p>\n<ul class=\"wp-block-list\">\n<li>Insert &#8211; add new records\/tuples in table or tables.<\/li>\n<li>Update \u2013 alter records\/tuples already present.<\/li>\n<li>Delete \u2013 remove records\/tuples.<\/li>\n<\/ul>\n<p>Unlike Data Definition statements, DML statements are not auto-commit transactions by default. Hence,  we can undo the changes until we specifically \u201ccommit\u201d the transaction. These commands correspond to the \u201cCRUD\u201d (Create-retrieve-update-delete) statements that most applications have.<\/p>\n<h3 class=\"wp-block-heading\" id=\"h-2-1-what-is-a-transaction\">2.1 What is a Transaction?<\/h3>\n<p>A transaction simply put is a unit of work, which is performed in the order in a database. Transactions are used to propagate or reverse one or more changes done to a database. Transactions are used for DML statements and are extremely important to ensure data integrity. More on transactions in general <a href=\"https:\/\/en.wikipedia.org\/wiki\/Database_transaction\" target=\"_blank\" rel=\"noreferrer noopener\">here<\/a>.<\/p>\n<h2 class=\"wp-block-heading\" id=\"h-3-delete-syntax\">3. Delete Syntax<\/h2>\n<p>The Basic Delete Syntax is as follows:<\/p>\n<pre class=\"brush:sql\">DELETE FROM table_name WHERE condition;<\/pre>\n<p><\/p>\n<li>Table_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = the name of the relation\/table whose records need to be changed.<\/li>\n<li>Condition&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= To restrict the records updated.<\/li>\n<p>In the Syntax above, the condition is particularly important because if the condition is not present i.e. \u201cno where\u201d clause is mentioned, then all the records of the table will be deleted.<\/p>\n<h2 class=\"wp-block-heading\" id=\"h-4-sql-delete-query-in-mysql\">4. SQL Delete query in MySQL<\/h2>\n<p>According to MySQL, the delete statement syntax for a singe table is as follows:<\/p>\n<pre class=\"brush:sql\">DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]\n    [PARTITION (partition_name [, partition_name] ...)]\n    [WHERE where_condition]\n    [ORDER BY ...]\n    [LIMIT row_count]\n<\/pre>\n<p><\/p>\n<p> The Syntax for Multi table is as follows<\/p>\n<p><\/p>\n<pre class=\"brush:sql\">DELETE [LOW_PRIORITY] [QUICK] [IGNORE]\n    tbl_name[.*] [, tbl_name[.*]] ...\n    FROM table_references\n    [WHERE where_condition]\n\nDELETE [LOW_PRIORITY] [QUICK] [IGNORE]\n    FROM tbl_name[.*] [, tbl_name[.*]] ...\n    USING table_references\n    [WHERE where_condition]\n<\/pre>\n<p>The \u201cLOW_PRIORITY\u201d ,\u201dQUICK\u201d and \u201cIGNORE\u201d are the modifiers that are available with the Delete statement in MySQL.<\/p>\n<p>LOW_PRIORITY = This modifier makes the delete statement delay execution until no other connection is reading data from the table. This is used by table-level locking storage engines such as MyISAM, MERGE and MEMORY.<\/p>\n<p>QUICK = This modifier does not merge index leaves during delete. This helps speed up the process of deleting records and is specifically used for ISAM tables.<\/p>\n<p>IGNORE = This modifier enables the delete statement to ignore ignorable errors during delete and continue with the rest. Errors are returned as warnings.<\/p>\n<h3 class=\"wp-block-heading\" id=\"h-4-1-setup-for-examples\">4.1 Setup for examples:<\/h3>\n<p>For going through the examples related to Delete, we will consider the database called \u201cTestDB\u201d which has the following data and tables:<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" width=\"820\" height=\"378\" src=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/all_tables.jpg\" alt=\"SQL Delete - all tables\" class=\"wp-image-97943\" srcset=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/all_tables.jpg 820w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/all_tables-300x138.jpg 300w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/all_tables-768x354.jpg 768w\" sizes=\"(max-width: 820px) 100vw, 820px\" \/><figcaption> Table information<\/figcaption><\/figure>\n<\/div>\n<p>Along with these there is the employees2 table which is the exact replica of the \u2018employees\u2019 table. Also, the ratings table is a lookup table with a rating and a percentage hike in salary. For running the queries, we will use the MySQL Workbench, the documentation for which is available <a href=\"https:\/\/dev.mysql.com\/doc\/workbench\/en\/\" target=\"_blank\" rel=\"noreferrer noopener\">here<\/a>.<\/p>\n<h2 class=\"wp-block-heading\" id=\"h-5-sql-delete-examples\">5. SQL DELETE Examples<\/h2>\n<p>Here we will see the various delete statements and syntaxes. <div style=\"display:inline-block; margin: 15px 0;\"> <div id=\"adngin-JavaCodeGeeks_incontent_video-0\" style=\"display:inline-block;\"><\/div> <\/div><\/p>\n<h3 class=\"wp-block-heading\" id=\"h-5-1-delete-single-record\">5.1 Delete Single Record<\/h3>\n<p>To delete a single record, we would simply use a where clause which retrieves the exact record to delete. Hence, the query to delete a location with the id=7 is as follows:<\/p>\n<pre class=\"brush:sql\"> DELETE FROM DEPT_LOCATIONS WHERE LOCATION_ID=7;<\/pre>\n<p>The output of the above query is as follows:<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" width=\"810\" height=\"553\" src=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_single_row.jpg\" alt=\"SQL Delete - Delete a single row\" class=\"wp-image-97944\" srcset=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_single_row.jpg 810w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_single_row-300x205.jpg 300w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_single_row-768x524.jpg 768w\" sizes=\"(max-width: 810px) 100vw, 810px\" \/><figcaption>Delete a single row<\/figcaption><\/figure>\n<\/div>\n<h3 class=\"wp-block-heading\" id=\"h-5-2-delete-multiple-records\">5.2 Delete Multiple records<\/h3>\n<p>We need to delete all the employees whose email is null from the employees2 table. On checking we see that there are multiple such records. To delete all the records in one go, we use the query<\/p>\n<pre class=\"brush:sql\">DELETE FROM EMPLOYEES2 WHERE EMAIL IS NULL;<\/pre>\n<p>Output is<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" width=\"803\" height=\"808\" src=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_multiple_rows.jpg\" alt=\"SQL Delete - Delete multiple records\" class=\"wp-image-97945\" srcset=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_multiple_rows.jpg 803w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_multiple_rows-298x300.jpg 298w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_multiple_rows-150x150.jpg 150w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_multiple_rows-768x773.jpg 768w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_multiple_rows-70x70.jpg 70w\" sizes=\"(max-width: 803px) 100vw, 803px\" \/><figcaption>Delete multiple records<\/figcaption><\/figure>\n<\/div>\n<h3 class=\"wp-block-heading\" id=\"h-5-3-delete-from-tables-with-columns-used-as-foreign-keys\">5.3 Delete from Tables with columns used as Foreign keys <\/h3>\n<p>If a column from a table is used as a foreign key in some other table, then records from that table cannot be deleted unless the \u201creferencing\u201d table records are deleted. An example of the error thrown is as follows<\/p>\n<pre class=\"brush:sql\"> DELETE FROM EMPLOYEES WHERE EMP_ID = 23;<\/pre>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" width=\"820\" height=\"236\" src=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/cannot_delete_foreign_key.jpg\" alt=\"SQL Delete - Foreign key error on delete\" class=\"wp-image-97946\" srcset=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/cannot_delete_foreign_key.jpg 820w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/cannot_delete_foreign_key-300x86.jpg 300w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/cannot_delete_foreign_key-768x221.jpg 768w\" sizes=\"(max-width: 820px) 100vw, 820px\" \/><figcaption>Foreign key error on delete<\/figcaption><\/figure>\n<\/div>\n<p>The EMP_ID column is used as a foreign key in employee_dept table. The other workaround is to mark the foreign key as \u201cOn DELETE CASCADE.\u201d In such a case, when we delete the record from the \u201cparent\u201d i.e. in the above example employee table, the records from the employee_Dept table will automatically be deleted and the delete will be allowed.<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" width=\"810\" height=\"602\" src=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/ondelete_cascade.jpg\" alt=\"SQL Delete - Delete Foreign key on Delete Cascade\" class=\"wp-image-97947\" srcset=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/ondelete_cascade.jpg 810w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/ondelete_cascade-300x223.jpg 300w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/ondelete_cascade-768x571.jpg 768w\" sizes=\"(max-width: 810px) 100vw, 810px\" \/><figcaption>Delete Foreign key on Delete Cascade<\/figcaption><\/figure>\n<\/div>\n<h3 class=\"wp-block-heading\" id=\"h-5-4-delete-with-limit\">5.4 Delete with LIMIT<\/h3>\n<p>We can limit the number of records retrieved and hence deleted using the LIMIT clause added to a delete.<\/p>\n<pre class=\"brush:sql\">DELETE FROM EMPLOYEE_DEPT \nWHERE DEPT_ID=2 \nLIMIT 3;\n<\/pre>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" width=\"810\" height=\"584\" src=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_with_limit.jpg\" alt=\"SQL Delete - Delete with Limit clause\" class=\"wp-image-97948\" srcset=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_with_limit.jpg 810w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_with_limit-300x216.jpg 300w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_with_limit-768x554.jpg 768w\" sizes=\"(max-width: 810px) 100vw, 810px\" \/><figcaption>Delete with Limit clause<\/figcaption><\/figure>\n<\/div>\n<p>We can also do an Order by and get the records in a sorted order and then apply the LIMIT clause<\/p>\n<pre class=\"brush:sql\">DELETE FROM EMPLOYEE_DEPT \nWHERE DEPT_ID=2 \nORDER BY RECORD_ID DESC\nLIMIT 4;\n<\/pre>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" width=\"810\" height=\"697\" src=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_limit_order_by.jpg\" alt=\"SQL Delete - Delete with LIMIT and ORDER BY\" class=\"wp-image-97949\" srcset=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_limit_order_by.jpg 810w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_limit_order_by-300x258.jpg 300w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_limit_order_by-768x661.jpg 768w\" sizes=\"(max-width: 810px) 100vw, 810px\" \/><figcaption>Delete with LIMIT and ORDER BY<\/figcaption><\/figure>\n<\/div>\n<h3 class=\"wp-block-heading\" id=\"h-5-5-delete-records-from-multiple-tables-using-inner-joins\">5.5 Delete records from multiple tables using INNER JOINS<\/h3>\n<p>We can use JOINs to connect multiple tables and delete records from them. The tables mentioned in the DELETE Clause are the ones from which records are deleted and The records from the rest of the tables are not deleted<\/p>\n<p>The Query to delete records from two tables using an inner join is as follows<\/p>\n<pre class=\"brush:sql\">DELETE EMPLOYEES, EMPLOYEE_DEPT\nFROM EMPLOYEES INNER JOIN EMPLOYEE_DEPT \nON EMPLOYEES.EMP_ID = EMPLOYEE_DEPT.EMP_ID\nWHERE EMPLOYEES.EMP_ID=23;\n<\/pre>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" width=\"810\" height=\"771\" src=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_with_inner_join.jpg\" alt=\"Delete from both tables with Inner Join\" class=\"wp-image-97950\" srcset=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_with_inner_join.jpg 810w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_with_inner_join-300x286.jpg 300w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_with_inner_join-768x731.jpg 768w\" sizes=\"(max-width: 810px) 100vw, 810px\" \/><figcaption>Delete from both tables with Inner Join <\/figcaption><\/figure>\n<\/div>\n<p>The Query to delete records from a single table using an inner join is as follows:<\/p>\n<pre class=\"brush:sql\">DELETE EMPLOYEE_DEPT\nFROM EMPLOYEES INNER JOIN EMPLOYEE_DEPT \nON EMPLOYEES.EMP_ID = EMPLOYEE_DEPT.EMP_ID\nWHERE EMPLOYEES.EMP_ID=3 AND EMPLOYEE_DEPT.TO_DATE IS NOT NULL;\n<\/pre>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" width=\"802\" height=\"724\" src=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/select_query_INNER_JOIN_SINGLE_TABLE.jpg\" alt=\"Records for INNER JOIN\" class=\"wp-image-97957\" srcset=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/select_query_INNER_JOIN_SINGLE_TABLE.jpg 802w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/select_query_INNER_JOIN_SINGLE_TABLE-300x271.jpg 300w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/select_query_INNER_JOIN_SINGLE_TABLE-768x693.jpg 768w\" sizes=\"(max-width: 802px) 100vw, 802px\" \/><figcaption>Records for INNER JOIN<\/figcaption><\/figure>\n<\/div>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" width=\"820\" height=\"540\" src=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_single_table_inner_join.jpg\" alt=\"Delete from single table with Inner join\" class=\"wp-image-97951\" srcset=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_single_table_inner_join.jpg 820w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_single_table_inner_join-300x198.jpg 300w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_single_table_inner_join-768x506.jpg 768w\" sizes=\"(max-width: 820px) 100vw, 820px\" \/><figcaption>Delete from single table with Inner join<\/figcaption><\/figure>\n<\/div>\n<h3 class=\"wp-block-heading\" id=\"h-5-6-delete-using-left-joins\">5.6 Delete using LEFT JOINS<\/h3>\n<p>We can also use LEFT JOINs to connect two tables and to delete records. The same rule applies as Inner Joins. Records are deleted only from the tables which are mentioned in the Delete Clause<\/p>\n<pre class=\"brush:sql\">DELETE EMPLOYEES \nFROM EMPLOYEES LEFT JOIN RATINGS\nON EMPLOYEES.RATING = RATINGS.RATING_ID\nWHERE RATINGS.RATING_ID=1;\n<\/pre>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" width=\"780\" height=\"797\" src=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_LEFT_JOIN.jpg\" alt=\"Delete from table with LEFT Join\" class=\"wp-image-97952\" srcset=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_LEFT_JOIN.jpg 780w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_LEFT_JOIN-294x300.jpg 294w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_LEFT_JOIN-768x785.jpg 768w\" sizes=\"(max-width: 780px) 100vw, 780px\" \/><figcaption>Delete from table with LEFT Join<\/figcaption><\/figure>\n<\/div>\n<h3 class=\"wp-block-heading\" id=\"h-5-7-delete-using-subqueries\">5.7 Delete using Subqueries<\/h3>\n<p>We cannot use the same table we are deleting records from, in a subquery. As an example<\/p>\n<pre class=\"brush:sql\">DELETE FROM EMPLOYEES2 WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEES2 WHERE EMAIL IS NULL);<\/pre>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" width=\"810\" height=\"334\" src=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_subquery_nosame_table.jpg\" alt=\"Error for same table in Subquery\" class=\"wp-image-97953\" srcset=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_subquery_nosame_table.jpg 810w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_subquery_nosame_table-300x124.jpg 300w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_subquery_nosame_table-768x317.jpg 768w\" sizes=\"(max-width: 810px) 100vw, 810px\" \/><figcaption>Error for same table in Subquery<\/figcaption><\/figure>\n<\/div>\n<h3 class=\"wp-block-heading\" id=\"h-5-8-delete-and-auto-increment\">5.8 Delete and Auto-Increment<\/h3>\n<p>Auto-increment indexes are not re-used in Inno-DB Engine systems. So, if a particular record is deleted, then that auto-increment value is not used again unless specifically set. To understand this , we deleted the Max location_id from the DEPT_LOCATIONS table. Then, we will insert a new record using the \u201cDefault\u201d value i.e. auto-incremented value of the location_id.<\/p>\n<pre class=\"brush:sql\">SELECT MAX(LOCATION_ID) FROM DEPT_LOCATIONS;\n\n#DELETE QUERY TO DELETE MAX LOCATION ID\nDELETE FROM DEPT_LOCATIONS WHERE LOCATION_ID=9;\n\n#INSERT A NEW RECORD WITH DEFAULT VALUE FOR LOCATION_ID WHICH IS AUTO-INCREMENTED.\nINSERT INTO DEPT_LOCATIONS VALUES(DEFAULT,9,'MINAS TIRITH');\n\nSELECT * FROM DEPT_LOCATIONS ORDER BY LOCATION_ID DESC;\n<\/pre>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" width=\"506\" height=\"771\" src=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_auto_increment_Value.jpg\" alt=\"Delete and Auto-increment\" class=\"wp-image-97954\" srcset=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_auto_increment_Value.jpg 506w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_auto_increment_Value-197x300.jpg 197w\" sizes=\"(max-width: 506px) 100vw, 506px\" \/><figcaption>Delete and Auto-increment<\/figcaption><\/figure>\n<\/div>\n<p>To reset the Auto-increment column value or to set an Auto-incremented column to a particular value, we can use the alter table syntax. For example<\/p>\n<pre class=\"brush:sql\">ALTER TABLE DEPT_LOCATIONS AUTO_INCREMENT = 9;<\/pre>\n<p>More about how and why of Auto-increment is available <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/innodb-auto-increment-handling.html\" target=\"_blank\" rel=\"noreferrer noopener\">here<\/a>.<\/p>\n<h3 class=\"wp-block-heading\" id=\"h-5-9-delete-all-records\">5.9 Delete all records<\/h3>\n<p>Delete all the records is also possible by omitting the Where clause. This however is usually not recommended and in some ways it is similar to doing a \u201cTruncate\u201d table. However, they are not the same concept at all.<\/p>\n<pre class=\"brush:sql\">DELETE FROM EMPLOYEES2;<\/pre>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" width=\"551\" height=\"638\" src=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_all.jpg\" alt=\"Delete all records\" class=\"wp-image-97955\" srcset=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_all.jpg 551w, https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_all-259x300.jpg 259w\" sizes=\"(max-width: 551px) 100vw, 551px\" \/><figcaption>Delete all records<\/figcaption><\/figure>\n<\/div>\n<h2 class=\"wp-block-heading\" id=\"h-6-summary\">6. Summary<\/h2>\n<p>In the article, we saw syntaxes related to the SQL Delete statement in MySQL. Delete is one of the important CRUD operations and these operations form an integral part of any application. The complete documentation of the Delete Clause is available <a rel=\"noreferrer noopener\" href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/delete.html\" target=\"_blank\">here<\/a>.<\/p>\n<h2 class=\"wp-block-heading\" id=\"h-7-download-the-source-code\">7. Download the Source Code<\/h2>\n<p>This was an example of the SQL Delete Query using MySQL RDBMS. Besides the delete Queries, there are a few Queries to know what foreign key constraints are present on your tables. Also, the queries for how to alter foreign key constraints for tables are also present. &nbsp;&nbsp;<\/p>\n<div class=\"download\"><strong>Download<\/strong><br \/>\nYou can download the full source code of this example here: <a href=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/12\/delete_queries.zip\"><strong> SQL Delete Query Example <\/strong><\/a><\/div>\n<p><strong>Last updated on Nov. 07th, 2021<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>1. Introduction In this article, we will look at one of the Data Manipulation Language (DML) constructs or statements called SQL Delete query using the MySQL RDBMS. 2. What is Data Manipulation Language? In SQL, Data Manipulation language consists of SQL data-change statements that modify data but not the underlying schema or database objects. The &hellip;<\/p>\n","protected":false},"author":232,"featured_media":1204,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[53],"tags":[647],"class_list":["post-97941","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-mysql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQL Delete Query Example - Java Code Geeks<\/title>\n<meta name=\"description\" content=\"Commands included in the data manipulation language: Insert, Update \u2013 alter records\/tuples already present, Delete \u2013 remove records\/tuples.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Delete Query Example - Java Code Geeks\" \/>\n<meta property=\"og:description\" content=\"Commands included in the data manipulation language: Insert, Update \u2013 alter records\/tuples already present, Delete \u2013 remove records\/tuples.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/\" \/>\n<meta property=\"og:site_name\" content=\"Examples Java Code Geeks\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/javacodegeeks\" \/>\n<meta property=\"article:published_time\" content=\"2020-12-14T09:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-11-07T21:37:35+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2012\/12\/java-logo.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"150\" \/>\n\t<meta property=\"og:image:height\" content=\"150\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Reshma Sathe\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@javacodegeeks\" \/>\n<meta name=\"twitter:site\" content=\"@javacodegeeks\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Reshma Sathe\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/\"},\"author\":{\"name\":\"Reshma Sathe\",\"@id\":\"https:\/\/examples.javacodegeeks.com\/#\/schema\/person\/079aa9a12c7b8ebea3391ebeb6036a74\"},\"headline\":\"SQL Delete Query Example\",\"datePublished\":\"2020-12-14T09:00:00+00:00\",\"dateModified\":\"2021-11-07T21:37:35+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/\"},\"wordCount\":1172,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/examples.javacodegeeks.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2012\/12\/java-logo.jpg\",\"keywords\":[\"mysql\"],\"articleSection\":[\"sql\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/\",\"url\":\"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/\",\"name\":\"SQL Delete Query Example - Java Code Geeks\",\"isPartOf\":{\"@id\":\"https:\/\/examples.javacodegeeks.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2012\/12\/java-logo.jpg\",\"datePublished\":\"2020-12-14T09:00:00+00:00\",\"dateModified\":\"2021-11-07T21:37:35+00:00\",\"description\":\"Commands included in the data manipulation language: Insert, Update \u2013 alter records\/tuples already present, Delete \u2013 remove records\/tuples.\",\"breadcrumb\":{\"@id\":\"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#primaryimage\",\"url\":\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2012\/12\/java-logo.jpg\",\"contentUrl\":\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2012\/12\/java-logo.jpg\",\"width\":150,\"height\":150,\"caption\":\"Bipartite Graph\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/examples.javacodegeeks.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Java Development\",\"item\":\"https:\/\/examples.javacodegeeks.com\/category\/java-development\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Core Java\",\"item\":\"https:\/\/examples.javacodegeeks.com\/category\/java-development\/core-java\/\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"sql\",\"item\":\"https:\/\/examples.javacodegeeks.com\/category\/java-development\/core-java\/sql\/\"},{\"@type\":\"ListItem\",\"position\":5,\"name\":\"SQL Delete Query Example\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/examples.javacodegeeks.com\/#website\",\"url\":\"https:\/\/examples.javacodegeeks.com\/\",\"name\":\"Java Code Geeks\",\"description\":\"Java Examples and Code Snippets\",\"publisher\":{\"@id\":\"https:\/\/examples.javacodegeeks.com\/#organization\"},\"alternateName\":\"JCG\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/examples.javacodegeeks.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/examples.javacodegeeks.com\/#organization\",\"name\":\"Exelixis Media P.C.\",\"url\":\"https:\/\/examples.javacodegeeks.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/examples.javacodegeeks.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2022\/06\/exelixis-logo.png\",\"contentUrl\":\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2022\/06\/exelixis-logo.png\",\"width\":864,\"height\":246,\"caption\":\"Exelixis Media P.C.\"},\"image\":{\"@id\":\"https:\/\/examples.javacodegeeks.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/javacodegeeks\",\"https:\/\/x.com\/javacodegeeks\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/examples.javacodegeeks.com\/#\/schema\/person\/079aa9a12c7b8ebea3391ebeb6036a74\",\"name\":\"Reshma Sathe\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/examples.javacodegeeks.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/08\/reshma_sathe-96x96.png\",\"contentUrl\":\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/08\/reshma_sathe-96x96.png\",\"caption\":\"Reshma Sathe\"},\"description\":\"I am a recent Master of Computer Science degree graduate from the University Of Illinois at Urbana-Champaign.I have previously worked as a Software Engineer with projects ranging from production support to programming and software engineering.I am currently working on self-driven projects in Java, Python and Angular and also exploring other frontend and backend technologies.\",\"sameAs\":[\"www.linkedin.com\/in\/reshma-sathe\"],\"url\":\"https:\/\/examples.javacodegeeks.com\/author\/reshma-sathe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Delete Query Example - Java Code Geeks","description":"Commands included in the data manipulation language: Insert, Update \u2013 alter records\/tuples already present, Delete \u2013 remove records\/tuples.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/","og_locale":"en_US","og_type":"article","og_title":"SQL Delete Query Example - Java Code Geeks","og_description":"Commands included in the data manipulation language: Insert, Update \u2013 alter records\/tuples already present, Delete \u2013 remove records\/tuples.","og_url":"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/","og_site_name":"Examples Java Code Geeks","article_publisher":"https:\/\/www.facebook.com\/javacodegeeks","article_published_time":"2020-12-14T09:00:00+00:00","article_modified_time":"2021-11-07T21:37:35+00:00","og_image":[{"width":150,"height":150,"url":"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2012\/12\/java-logo.jpg","type":"image\/jpeg"}],"author":"Reshma Sathe","twitter_card":"summary_large_image","twitter_creator":"@javacodegeeks","twitter_site":"@javacodegeeks","twitter_misc":{"Written by":"Reshma Sathe","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#article","isPartOf":{"@id":"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/"},"author":{"name":"Reshma Sathe","@id":"https:\/\/examples.javacodegeeks.com\/#\/schema\/person\/079aa9a12c7b8ebea3391ebeb6036a74"},"headline":"SQL Delete Query Example","datePublished":"2020-12-14T09:00:00+00:00","dateModified":"2021-11-07T21:37:35+00:00","mainEntityOfPage":{"@id":"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/"},"wordCount":1172,"commentCount":0,"publisher":{"@id":"https:\/\/examples.javacodegeeks.com\/#organization"},"image":{"@id":"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#primaryimage"},"thumbnailUrl":"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2012\/12\/java-logo.jpg","keywords":["mysql"],"articleSection":["sql"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/","url":"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/","name":"SQL Delete Query Example - Java Code Geeks","isPartOf":{"@id":"https:\/\/examples.javacodegeeks.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#primaryimage"},"image":{"@id":"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#primaryimage"},"thumbnailUrl":"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2012\/12\/java-logo.jpg","datePublished":"2020-12-14T09:00:00+00:00","dateModified":"2021-11-07T21:37:35+00:00","description":"Commands included in the data manipulation language: Insert, Update \u2013 alter records\/tuples already present, Delete \u2013 remove records\/tuples.","breadcrumb":{"@id":"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#primaryimage","url":"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2012\/12\/java-logo.jpg","contentUrl":"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2012\/12\/java-logo.jpg","width":150,"height":150,"caption":"Bipartite Graph"},{"@type":"BreadcrumbList","@id":"https:\/\/examples.javacodegeeks.com\/sql-delete-query-example\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/examples.javacodegeeks.com\/"},{"@type":"ListItem","position":2,"name":"Java Development","item":"https:\/\/examples.javacodegeeks.com\/category\/java-development\/"},{"@type":"ListItem","position":3,"name":"Core Java","item":"https:\/\/examples.javacodegeeks.com\/category\/java-development\/core-java\/"},{"@type":"ListItem","position":4,"name":"sql","item":"https:\/\/examples.javacodegeeks.com\/category\/java-development\/core-java\/sql\/"},{"@type":"ListItem","position":5,"name":"SQL Delete Query Example"}]},{"@type":"WebSite","@id":"https:\/\/examples.javacodegeeks.com\/#website","url":"https:\/\/examples.javacodegeeks.com\/","name":"Java Code Geeks","description":"Java Examples and Code Snippets","publisher":{"@id":"https:\/\/examples.javacodegeeks.com\/#organization"},"alternateName":"JCG","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/examples.javacodegeeks.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/examples.javacodegeeks.com\/#organization","name":"Exelixis Media P.C.","url":"https:\/\/examples.javacodegeeks.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/examples.javacodegeeks.com\/#\/schema\/logo\/image\/","url":"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2022\/06\/exelixis-logo.png","contentUrl":"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2022\/06\/exelixis-logo.png","width":864,"height":246,"caption":"Exelixis Media P.C."},"image":{"@id":"https:\/\/examples.javacodegeeks.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/javacodegeeks","https:\/\/x.com\/javacodegeeks"]},{"@type":"Person","@id":"https:\/\/examples.javacodegeeks.com\/#\/schema\/person\/079aa9a12c7b8ebea3391ebeb6036a74","name":"Reshma Sathe","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/examples.javacodegeeks.com\/#\/schema\/person\/image\/","url":"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/08\/reshma_sathe-96x96.png","contentUrl":"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2020\/08\/reshma_sathe-96x96.png","caption":"Reshma Sathe"},"description":"I am a recent Master of Computer Science degree graduate from the University Of Illinois at Urbana-Champaign.I have previously worked as a Software Engineer with projects ranging from production support to programming and software engineering.I am currently working on self-driven projects in Java, Python and Angular and also exploring other frontend and backend technologies.","sameAs":["www.linkedin.com\/in\/reshma-sathe"],"url":"https:\/\/examples.javacodegeeks.com\/author\/reshma-sathe\/"}]}},"_links":{"self":[{"href":"https:\/\/examples.javacodegeeks.com\/wp-json\/wp\/v2\/posts\/97941","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/examples.javacodegeeks.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/examples.javacodegeeks.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/examples.javacodegeeks.com\/wp-json\/wp\/v2\/users\/232"}],"replies":[{"embeddable":true,"href":"https:\/\/examples.javacodegeeks.com\/wp-json\/wp\/v2\/comments?post=97941"}],"version-history":[{"count":0,"href":"https:\/\/examples.javacodegeeks.com\/wp-json\/wp\/v2\/posts\/97941\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/examples.javacodegeeks.com\/wp-json\/wp\/v2\/media\/1204"}],"wp:attachment":[{"href":"https:\/\/examples.javacodegeeks.com\/wp-json\/wp\/v2\/media?parent=97941"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/examples.javacodegeeks.com\/wp-json\/wp\/v2\/categories?post=97941"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/examples.javacodegeeks.com\/wp-json\/wp\/v2\/tags?post=97941"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}