In the database structured query language ( SQL ), the DELETE statement is used to remove one or more records from a table . A subset may be defined for deletion using a condition, otherwise all records are removed. Some database management systems (DBMSs), like MySQL , allow deletion of rows from multiple tables with one DELETE statement (this is sometimes called multi-table DELETE).
6-411: Delete rows from table pies where column flavor equals Lemon Meringue : Delete rows in trees , if the value of height is smaller than 80. Delete all rows from mytable : Delete rows from mytable using a subquery in the where condition: Delete rows from mytable using a list of values: Suppose there is a simple database that lists people and addresses. More than one person can live at
12-464: A particular address and a person can live at more than one address (this is an example of a many-to-many relationship ). The database only has three tables, person , address , and pa , with the following data: The pa table relates the person and address tables, showing that Joe, Bob and Ann all live at 2001 Main Street, but Joe also takes up residence on Pico Boulevard. In order to remove joe from
18-422: A single, implicitly structured data item in a table . A database table can be thought of as consisting of rows and columns . Each row in a table represents a set of related data, and every row in the table has the same structure. For example, in a table that represents companies, each row might represent a single company. Columns might represent things like company name, address, etc. In a table that represents
24-456: A table can be very time-consuming. Some DBMS offer a TRUNCATE TABLE command that works a lot quicker, as it only alters metadata and typically does not spend time enforcing constraints or firing triggers. DELETE only deletes the rows. For deleting a table entirely the DROP command can be used. Row (database) In a relational database , a row , a.k.a. record or tuple , represents
30-413: The association of employees with departments, each row would associate one employee with one department. The implicit structure of a row, and the meaning of the data values in a row, requires that the row be understood as providing a succession of data values, one in each column of the table. The row is then interpreted as a relvar composed of a set of tuples, with each tuple consisting of the two items:
36-426: The database, two deletes must be executed: To maintain referential integrity, Joe's records must be removed from both person and pa . It could be that beyond just having three tables, the database also has been set up with a trigger so that whenever a row is deleted from person any linked rows would be deleted from pa . Then the first statement: would automatically trigger the second: Deleting all rows from
#412587