Switch to: V10V9V8V7V6V5

DELETE

Deletes the chosen records from the table.

delete_statement_searched
		: DELETE FROM table_name [ WHERE search_condition ]

In FROM statement you should include the table which contains the records to be deleted. In WHERE statement you should include the selection criterion of the records to be deleted.

	DELETE FROM tbl1 WHERE fldLong  > 1000

If the WHERE statement is not defined all the table records will be deleted, the table becomes blank but it is not deleted from database.

	DELETE FROM tbl1

DELETE with a Subquery.

Sometimes it is necessary to select records rest on the data from the other tables. For this item one can use the selection conditions with the subquery.

Child queries in the WHERE statement can have several levels of embedding. They also can contain foreign links on the object table of DELETE command. Foreign links are met very often as they implement the “uniting” of the subquery tables (table) and the object table of DELETE command.


The sample of scalar subquery:

	DELETE FROM orders 
	WHERE rep = ( SELECT empl_num
			FROM salesreps
			WHERE name = ‘ Sue Smith’)
 
	DELETE FROM salesreps
	WHERE ( .02* quota ) > ( SELECT sum(amount )
			FROM orders
			WHERE rep = empl_num )

Tha sample of multi recorded subquery:

	DELETE FROM customers
	WHERE cust_rep IN (SELECT empl_num
				FROM salesreps
				WHERE sales < ( .8* quota))