You can execute the DELETE statements to delete the rows that meet the conditions from tables.
Syntax
The following DELETE statements delete the rows that meet the conditions specified
by where_condition from the tables specified by tbl_name, and return the number of deleted rows. If you do not specify the WHERE conditions,
all the data in the specified tables is deleted.
- Single logical table.
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM [schema_name.]tbl_name [WHERE where_condition] - Multiple logical tables.
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[. *]] ... FROM table_references [WHERE where_condition] DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM [schema_name.]tbl_name[.*] [, [schema_name.]tbl_name[. *]] ... USING table_references [WHERE where_condition]
- The DELETE statements support the following modifiers:
- If you specify LOW_PRIORITY, the DELETE operation is performed after all the read operations from the table are completed.
- If you specify IGNORE, the errors are ignored during the deletion process.
- QUICK is related to the storage engines of MySQL. For more information, see MySQL documentation.
- Each modifier in the DELETE statements is pushed down to the storage layer MySQL and remains unchanged. This process does not affect the modifier operations of PolarDB-X 1.0.
Limits on syntax
Compared with the DELETE syntax of the native MySQL, the DELETE syntax of PolarDB-X 1.0 has the following limits:
By default, a DELETE statement is forbidden if the statement needs to delete more than 10,000 rows and the statement cannot be pushed down. In this case, you must use hints so that DELETE statement can be supported, as shown in the following example:
DELETE FROM t1 ORDER BY name LIMIT 10001;
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.name LIMIT 10001;
DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.name LIMIT 10001;