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 and 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

Festivals and Fairs (USA)