The DELETE command allows you to remove rows from a table. This command deletes the specified rows from the table based on the given condition.
Syntax
The syntax for a DELETE statement is:
DELETE FROM table_name [AS alias] WHERE condition
Parameters
FROM table_name: This is where you specify the table that you want to delete rows from.AS alias: This is an optional part of the syntax. You can create a temporary name for tables using it. Anywhere that table is then referred to in the query can use this alias.WHERE condition: This is where you specify the conditions that must be met for rows to be deleted. If the condition is not met, no rows will be deleted.
Example
The following SQL statement deletes the employee with id 3 from the employees table:
DELETE FROM employees
WHERE id = 3;
In more complex scenarios, the alias can be quite useful. For example, if you have an employees table with a self-referential manager_id column, you might want to delete all employees who have a manager with a salary less than 50000. You could accomplish this with a DELETE statement using an alias like this:
DELETE FROM employees AS e1
WHERE manager_id IN (
SELECT id FROM employees AS e2
WHERE e2.salary < 50000
);
In this query, e1 and e2 are aliases for the employees table. The e2 alias is used in the inner SELECT query to find the ids of all managers with a salary less than 50000. The e1 alias is then used in the outer DELETE query to delete all employees whose manager_id matches one of these ids.
To delete all records from a table, you can use the DELETE statement without a WHERE clause:
DELETE FROM employees;
This statement will delete all records in the employees table.
Comparison with TRUNCATE
The DELETE statement and the TRUNCATE statement are both used to delete data from a table. However, they work in different ways:
-
The
DELETEcommand removes rows one at a time and records an entry in the transaction log for each deleted row. Because of this, it’s slower thanTRUNCATEwhen it comes to deleting all rows from a table. However, it allows you to specify aWHEREclause to filter which rows should be removed. -
The
TRUNCATEcommand is a faster way to empty a table completely because it doesn’t log individual row deletions. However, it doesn’t allow for the specification of aWHEREclause and removes all rows.
Learn more about the TRUNCATE command in the corresponding documentation.
Rollback
The DELETE statement in Polypheny supports rollback. This means that if you delete some rows and then decide that you need to undo that operation, you can roll back the transaction to get the deleted rows back, as long as the transaction hasn’t been committed yet. This is another feature that differentiates DELETE from TRUNCATE, as the TRUNCATE operation cannot be rolled back.
