(mysql.info) delete

 
 13.2.1 `DELETE' Syntax
 ----------------------
 
 Single-table syntax:
 
      DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM TBL_NAME
          [WHERE WHERE_CONDITION]
          [ORDER BY ...]
          [LIMIT ROW_COUNT]
 
 Multiple-table syntax:
 
      DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
          TBL_NAME[.*] [, TBL_NAME[.*]] ...
          FROM TABLE_REFERENCES
          [WHERE WHERE_CONDITION]
 
 Or:
 
      DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
          FROM TBL_NAME[.*] [, TBL_NAME[.*]] ...
          USING TABLE_REFERENCES
          [WHERE WHERE_CONDITION]
 
 For the single-table syntax, the `DELETE' statement deletes rows from
 TBL_NAME and returns the number of rows deleted. The `WHERE' clause, if
 given, specifies the conditions that identify which rows to delete.
 With no `WHERE' clause, all rows are deleted. If the `ORDER BY' clause
 is specified, the rows are deleted in the order that is specified. The
 `LIMIT' clause places a limit on the number of rows that can be deleted.
 
 For the multiple-table syntax, `DELETE' deletes from each TBL_NAME the
 rows that satisfy the conditions. In this case, `ORDER BY' and `LIMIT'
 cannot be used.
 
 WHERE_CONDITION is an expression that evaluates to true for each row to
 be deleted. It is specified as described in  select.
 
 As stated, a `DELETE' statement with no `WHERE' clause deletes all
 rows. A faster way to do this, when you do not want to know the number
 of deleted rows, is to use `TRUNCATE TABLE'. See  truncate.
 
 If you delete the row containing the maximum value for an
 `AUTO_INCREMENT' column, the value is reused later for a `BDB' table,
 but not for a `MyISAM' or `InnoDB' table. If you delete all rows in the
 table with `DELETE FROM TBL_NAME' (without a `WHERE' clause) in
 `AUTOCOMMIT' mode, the sequence starts over for all storage engines
 except `InnoDB' and `MyISAM'. There are some exceptions to this
 behavior for `InnoDB' tables, as discussed in 
 innodb-auto-increment-column.
 
 For `MyISAM' and `BDB' tables, you can specify an `AUTO_INCREMENT'
 secondary column in a multiple-column key. In this case, reuse of values
 deleted from the top of the sequence occurs even for `MyISAM' tables.
 See  example-auto-increment.
 
 The `DELETE' statement supports the following modifiers:
 
    * If you specify `LOW_PRIORITY', the server delays execution of the
      `DELETE' until no other clients are reading from the table.
 
    * For `MyISAM' tables, if you use the `QUICK' keyword, the storage
      engine does not merge index leaves during delete, which may speed
      up some kinds of delete operations.
 
    * The `IGNORE' keyword causes MySQL to ignore all errors during the
      process of deleting rows. (Errors encountered during the parsing
      stage are processed in the usual manner.) Errors that are ignored
      due to the use of `OPTION' are returned as warnings.
 
 The speed of delete operations may also be affected by factors
 discussed in  delete-speed.
 
 In `MyISAM' tables, deleted rows are maintained in a linked list and
 subsequent `INSERT' operations reuse old row positions. To reclaim
 unused space and reduce file sizes, use the `OPTIMIZE TABLE' statement
 or the `myisamchk' utility to reorganize tables. `OPTIMIZE TABLE' is
 easier, but `myisamchk' is faster. See  optimize-table, and
  myisamchk.
 
 The `QUICK' modifier affects whether index leaves are merged for delete
 operations. `DELETE QUICK' is most useful for applications where index
 values for deleted rows are replaced by similar index values from rows
 inserted later. In this case, the holes left by deleted values are
 reused.
 
 `DELETE QUICK' is not useful when deleted values lead to underfilled
 index blocks spanning a range of index values for which new inserts
 occur again. In this case, use of `QUICK' can lead to wasted space in
 the index that remains unreclaimed. Here is an example of such a
 scenario:
 
   1. Create a table that contains an indexed `AUTO_INCREMENT' column.
 
   2. Insert many rows into the table. Each insert results in an index
      value that is added to the high end of the index.
 
   3. Delete a block of rows at the low end of the column range using
      `DELETE QUICK'.
 
 In this scenario, the index blocks associated with the deleted index
 values become underfilled but are not merged with other index blocks
 due to the use of `QUICK'. They remain underfilled when new inserts
 occur, because new rows does not have index values in the deleted
 range. Furthermore, they remain underfilled even if you later use
 `DELETE' without `QUICK', unless some of the deleted index values
 happen to lie in index blocks within or adjacent to the underfilled
 blocks. To reclaim unused index space under these circumstances, use
 `OPTIMIZE TABLE'.
 
 If you are going to delete many rows from a table, it might be faster
 to use `DELETE QUICK' followed by `OPTIMIZE TABLE'. This rebuilds the
 index rather than performing many index block merge operations.
 
 The MySQL-specific `LIMIT ROW_COUNT' option to `DELETE' tells the
 server the maximum number of rows to be deleted before control is
 returned to the client.  This can be used to ensure that a given
 `DELETE' statement does not take too much time.  You can simply repeat
 the `DELETE' statement until the number of affected rows is less than
 the `LIMIT' value.
 
 If the `DELETE' statement includes an `ORDER BY' clause, the rows are
 deleted in the order specified by the clause. This is really useful
 only in conjunction with `LIMIT'. For example, the following statement
 finds rows matching the `WHERE' clause, sorts them by
 `timestamp_column', and deletes the first (oldest) one:
 
      DELETE FROM somelog WHERE user = 'jcole'
      ORDER BY timestamp_column LIMIT 1;
 
 You can specify multiple tables in a `DELETE' statement to delete rows
 from one or more tables depending on the particular condition in the
 `WHERE' clause.  However, you cannot use `ORDER BY' or `LIMIT' in a
 multiple-table `DELETE'. The TABLE_REFERENCES clause lists the tables
 involved in the join. Its syntax is described in  join.
 
 For the first multiple-table syntax, only matching rows from the tables
 listed before the `FROM' clause are deleted. For the second
 multiple-table syntax, only matching rows from the tables listed in the
 `FROM' clause (before the `USING' clause) are deleted.  The effect is
 that you can delete rows from many tables at the same time and have
 additional tables that are used only for searching:
 
      DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
 
 Or:
 
      DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
 
 These statements use all three tables when searching for rows to
 delete, but delete matching rows only from tables `t1' and `t2'.
 
 The preceding examples show inner joins that use the comma operator,
 but multiple-table `DELETE' statements can use any type of join allowed
 in `SELECT' statements, such as `LEFT JOIN'.
 
 The syntax allows `.*' after the table names for compatibility with
 `Access'.
 
 If you use a multiple-table `DELETE' statement involving `InnoDB'
 tables for which there are foreign key constraints, the MySQL optimizer
 might process tables in an order that differs from that of their
 parent/child relationship. In this case, the statement fails and rolls
 back.  Instead, you should delete from a single table and rely on the
 `ON DELETE' capabilities that `InnoDB' provides to cause the other
 tables to be modified accordingly.
 
 * If you provide an alias for a table, you must use the alias
 when referring to the table:
 
      DELETE t1 FROM test AS t1, test2 WHERE ...
 
 Cross-database deletes are supported for multiple-table deletes, but in
 this case, you must refer to the tables without using aliases. For
 example:
 
      DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...
 
 Currently, you cannot delete from a table and select from the same
 table in a subquery.
Info Catalog
(mysql.info) data-manipulation
(mysql.info) do
automatically generated byinfo2html