SQL (Structured Query Language) is a standard interactive and programming language for getting information from and updating a relational database. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, and Ingres. The standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database. For additional help and reference material


SQL Tutorial & Tips
Select | Insert | Update | Delete | Open Cursor | Fetch | Close Cursor | Union

 


Structured Query Language

                  Delete
Removes rows from a table.
 
Syntax:
 
DELETE [FROM] {table_name | view_name}
	[WHERE clause]
where
  table_name | view_name = 
  [[database_name.]owner.]{table_name | view_name}
 
  Specifies the table or view used in the DELETE statement.
  If the table or view exists in another database, use a fully
  qualified table_name or view_name (database_name.owner.object_name). 
 
  Because DELETE can affect only one base table at a time, you cannot
  use DELETE with a view that has a FROM clause naming more than
  one table. 


WHERE clause = WHERE {search_conditions | CURRENT OF cursor_name} Is used to perform a searched delete (using search_conditions) or a positioned delete (using CURRENT OF cursor_name). When no WHERE clause is given in the DELETE statement, all rows in the table are removed. The table itself, along with its indexes, constraints, and so on, remains in the database. search_conditions Specifies the criteria for a searched delete. For details, see the Search Conditions topic. The IDENTITYCOL keyword can be used in the place of a column_name that has the IDENTITY property. For more information, see the CREATE TABLE statement. CURRENT OF cursor_name Specifies the criteria for a positioned delete against the current row within the specified cursor. Cursor operations affect only the single row on which the cursor is positioned. For details, see the Cursors topic.
Examples: A. DELETE with No Parameters This example deletes all rows from the customer table. DELETE customer B. DELETE a Set of Rows Because cars may not be unique, this example deletes all rows where cars is Ford. DELETE FROM automobiles WHERE cars = 'Ford'
C. DELETE the Current Row of a Cursor This example shows a delete made against a cursor named complex_join_cursor. It affects only the single row that is currently fetched from the cursor. DELETE FROM authors WHERE CURRENT OF complex_join_cursor D. DELETE Based on a Subquery or Using the Transact-SQL Extension This example shows the Transact-SQL extension used to delete records from a base table based on a join or correlated subquery. The first DELETE shows the ANSI-compatible subquery solution, and the second DELETE shows the Transact-SQL extension. /* ANSI-Standard subquery */ DELETE FROM titleauthor WHERE au_id IN (SELECT a.au_id FROM authors a, titles t, titleauthor ta WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id AND t.title LIKE '%computers%') /* Transact-SQL extension */ DELETE FROM titleauthor FROM authors a, titles t WHERE a.au_id = titleauthor.au_id AND titleauthor.title_id = t.title_id AND t.title LIKE '%computers%'
Home   |   Business Directory