DELETE Statements

Estimated reading: 2 minutes 22 views

1. Write a query to delete specific rows from a table using a condition

To delete specific rows, use the DELETE statement with a WHERE clause that defines the condition.
Example:

				
					DELETE FROM employees 
WHERE id = 1;

2. What is the difference between DELETE and TRUNCATE? When would you use each?

DELETE: Removes specific rows based on a condition and can be rolled back (if using transactions). It is slower because it logs individual row deletions.
TRUNCATE: Removes all rows from a table quickly and cannot be rolled back. It also resets any auto-increment values and is generally used when you want to delete all data in a table.

Use DELETE: When you need to delete specific rows or need to retain the table structure. Use TRUNCATE: When you need to remove all rows quickly and don’t need to worry about transaction rollbacks.
Example:

				
					DELETE FROM employees WHERE id = 1; -- DELETE specific rows
TRUNCATE TABLE employees; -- TRUNCATE all rows

3. How do you handle deleting rows from a table that has foreign key constraints?

If the table has foreign key constraints, the deletion may fail if there are dependent rows in other tables. You can:

Use ON DELETE CASCADE: Automatically delete the dependent rows in child tables.
Delete rows in child tables first: Delete rows from the dependent tables before deleting from the parent table.

Example (with ON DELETE CASCADE):

				
					ALTER TABLE orders ADD CONSTRAINT fk_employee 
FOREIGN KEY (employee_id) 
REFERENCES employees(id) 
ON DELETE CASCADE;

4. Write an SQL query to delete duplicate rows from a table while keeping one copy

To delete duplicates, you can use a DELETE statement with a ROWID and a subquery to keep only one copy.
Example:

				
					DELETE FROM employees 
WHERE ROWID NOT IN (
    SELECT MIN(ROWID)
    FROM employees 
    GROUP BY name, role, salary
);

5. Explain the impact of omitting the WHERE clause in a DELETE statement

Omitting the WHERE clause in a DELETE statement results in the deletion of all rows in the table, which may lead to accidental loss of data.
Example:

				
					-- This will delete all rows in the employees table
DELETE FROM employees;