The DELETE statement deletes rows from a table, nickname, or view, or the underlying tables, nicknames, or views of the specified fullselect. Deleting a row from a nickname deletes the row from the data source object to which the nickname refers. Deleting a row from a view deletes the row from the table on which the view is based if no INSTEAD OF trigger is defined for the delete operation on this view. If such a trigger is defined, the trigger will be executed instead.
There are two forms of this statement, The Searched DELETE form is used to delete one or more rows (optionally determined by a search condition). The Positioned DELETE form is used to delete exactly one row (as determined by the current position of a cursor).
The DELETE Statement in SQL is used to delete existing records from a table. We can delete a single record or multiple records depending on the condition we specify in the WHERE clause.
Syntax
DELETE FROM table_name WHERE some_condition; table_name: name of the table some_condition: condition to choose particular record.
To delete records in a table in DB2 database DELETE command is used, this command is well known in other databases and their use is similar in DB2.
The DELETE statement allows you to delete one or more rows from a table. The following illustrates the syntax of the DELETE statement −
DELETE FROM table_name [WHERE condition];
Syntax
First, specify the name of the table from which you want to delete data.
Second, use a condition in the WHERE clause to specify which rows to delete. All rows that cause the condition to evaluate to true will be deleted.
The WHERE clause is optional. If you skip it, the DELETE statement will remove all rows from the target table. If no row satisfies the condition, the DELETE statement just does nothing.
Typically, a table is associated with another table via a relationship: one-to-one, one-to-many, or many-to-many. Depending on the setting of the foreign key constraint such as ON DELETE CASCADE, the DELETE statement will automatically delete the rows from the child table when a row from the parent table is deleted.
DELETE FROM [Table]
As shown in the above syntax, the DELETE command only needs to receive as parameter the name of the table, but there is a very important item to be considered. When using the command as shown before all records are deleted and in many cases this format does not apply, due the WHERE clause is used with it.
DELETE FROM [Table] WHERE [Conditional]
In the syntax shown earlier, only records that satisfy the conditional will be deleted. As a practical example we will delete products from a table using both formats.
Deletes an arbitrary number of rows from a Kudu table. This statement only works for Impala tables that use the Kudu storage engine.
Syntax
DELETE [FROM] [database_name.]table_name [ WHERE where_conditions ] DELETE table_ref FROM [joined_table_refs] [ WHERE where_conditions ]
The first form evaluates rows from one table against an optional WHERE clause, and deletes all the rows that match the WHERE conditions, or all rows if WHERE is omitted.
The second form evaluates one or more join clauses, and deletes all matching rows from one of the tables. The join clauses can include non-Kudu tables, but the table from which the rows are deleted must be a Kudu table. The FROM keyword is required in this case, to separate the name of the table whose rows are being deleted from the table names of the join clauses.