DELETE

• Databases


  A collection of data organized and structured in a way that allows for efficient storage, retrieval, and management of information
• Data Removal in SQL


  • DELETE
  • TRUNCATE
  • DROP

  
• DELETE


  Removes one or more rows from a table based on a condition specified in the WHERE clause
• TRUNCATE


  Quickly clears all table rows while retaining structure, often used during development or testing
• DROP


  Permanently eliminates an entire table and its data, used during schema redesigns or when decommissioning tables
• DELETE

  1. Deleting data (rows) from the database

  2. Removes one or more rows from a table based on a condition specified in the WHERE clause

  3. If the WHERE clause is omitted, all rows in the table will be deleted

  4. Ideal for deleting a few rows or when you need to selectively delete rows based on specific criteria

  5. Can be slower than TRUNCATE for large data sets because it logs individual row deletions

  6. Changes can be rolled back if the operation is performed within a transaction

  7. The table structure, including its columns, constraints, indexes, and so on, remains intact

  
• Some uses of DELETE


  • Delete one row
  • Delete all rows (but the table and its structure, attributes and indexes are not deleted)
  • Delete using %
• DELETE FROM … WHERE…
• DELETE FROM … WHERE…LIKE…
• DELETE FROM table [WHERE clause]
• DELETE FROM … WHERE…


  • Where (which table) to delete the data from
  • Which rows to match to delete?

  
• DELETE FROM Pet WHERE PetID = 109;

  
• DELETE statement does not return a result set as the SELECT statement. It only returns the number of rows deleted.

  
• To verify that the correct row was successfully deleted, perform an additional SELECT query to check the output table.

  
• DELETE FROM Pet ;
• Careful with WHERE - it's easy to mess up WHERE clauses. In a SELECT it means we don't get the data we want, in an UPDATE or DELETE it means we can break the database.

  
• DELETE multiple rows using wildcard %


  • DELETE FROM Pet WHERE Name LIKE '%anion'

  
• DELETE FROM…WHERE…IN…


  To delete multiple records using the IN operator

  
• DELETE FROM Pet WHERE PetID IN (101, 105, 107);
• SELECT * FROM Pet;
• DELETE FROM Pet WHERE Name = '%anion';
• DELETE vs TRUNCATE


  DELETE just removes the row data (the contents), can be selective (WHERE), and can be logged. TRUNCATE will empty the table and reset metadata, not selective or logged.

  
• Removing data using TRUNCATE, DROP


  1. TRUNCATE TABLE People;

  2. DROP TABLE People;
• DELETE, TRUNCATE, DROP


  • DELETE - for conditional row removal
  • TRUNCATE - for quickly clearing all table rows while retaining structure
  • DROP - for permanently eliminating an entire table and its data