DELETE FROM users;
The DELETE statement above will delete all of the rows from the 'users' table because no WHERE clause was used to limit or filter the rows to be matched.
In actual usage you will normally want to delete only table rows that match column values defined in the WHERE clause, as shown in the example below:
The query above deletes only rows where the name column contains only the text 'john'. (It would not, however, remove rows where the name column contained 'john smith'.)
The query above, which uses the LIKE keyword, would delete all rows where the name column contained the text 'john', as well as rows where the name column contained the text 'john smith', 'Hello john', 'mike johnson', etc.
Note that you can delete all of the rows in a table without deleting the table itself. The table structure, attributes, and indexes will still be intact even after all of the rows are removed from a table. This statement will delete all rows from the table:
DELETE FROM table_name;
However, deleting all rows from a table can be time consuming and resource intensive. In this case it is better to use the TRUNCATE (truncate) command. The TRUNCATE command is much faster than DELETE as it only alters metadata and typically does not expend any overhead enforcing constraints or firing TRIGGERs.
Our thanks to Piyush G for catching a typo on this page.
|© 2003 - 2024 psoug.org