DELETE command allows you to remove rows from a table. This command deletes the specified rows from the table based on the given condition.
The syntax for a
DELETE statement is:
DELETE FROM table_name [AS alias] WHERE condition
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.
The following SQL statement deletes the employee with
id 3 from the
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,
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
To delete all records from a table, you can use the
DELETE statement without a
DELETE FROM employees;
This statement will delete all records in the
DELETE statement and the
TRUNCATE statement are both used to delete data from a table. However, they work in different ways:
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 than
TRUNCATEwhen it comes to deleting all rows from a table. However, it allows you to specify a
WHEREclause to filter which rows should be removed.
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 a
WHEREclause and removes all rows.
Learn more about the
TRUNCATE command in the corresponding documentation.
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
TRUNCATE, as the
TRUNCATE operation cannot be rolled back.