Syntax
Parameters
Remarks
Updated rows are marked for deletion, but are not automatically cleaned up. You can monitor fragmentation ininformation_schema.tables to understand how many rows are marked for deletion out of total rows; fragmentation = (rows marked for deletion / total rows). Total row count in information_schema.tables excludes the number of rows marked for deletion. Query performance is not materially impacted by delete marks.
To mitigate fragmentation, use the VACUUM command to manually clean up deleted rows.
When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an update operation for the target table. When using FROM the join must produce at most one output row for each row to be modified. In other words, a target row shouldn’t join to more than one row from the other table(s).
Example with WHERE
The following example restocksproduct for which quantity is less than 10:
Table after:
Example updating multiple columns
This example applies a discount and updates the quantity of a specific product.
Table after:
Example updating with FROM
This example updates available stock count.product before:
Table
sales before:
Table
products after: