Implementation of MySQL multi-Table update and multi-Table Deletion

How can I implement MySQL multi-Table update? This is a problem that many people have mentioned. The following describes how to implement MySQL multi-Table update and multi-Table deletion. I hope this will help you.

MySQL multi-Table update
In MySQL 3.23, you can use LIMIT # to ensure that only the specified number of record rows is changed.

If an order by clause is used (supported since MySQL 4.0.0), the record row is updated in the specified ORDER. This is actually useful only with LIMIT.

From MySQL 4.0.4, you can also perform an UPDATE operation that contains multiple tables:

UPDATE items, month SET items. price = month. price
WHERE items. id = month. id;

Note: order by or LIMIT cannot be used for multi-Table UPDATE.

Delete multiple tables
The first multi-Table deletion format is supported since MySQL 4.0.0. The second multi-Table deletion format is supported since MySQL 4.0.2.

The matching record row in the table listed only before the FROM or USING clause is deleted. The result is that you want to delete record rows from multiple tables at the same time, and other tables can also be used for retrieval.

After the table name, only. * is used for compatibility with Access:

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

In the above case, we only delete matched record rows from Table t1 and table t2.

If an order by clause is used (supported since MySQL 4.0.0), the record row is deleted in the specified ORDER. This is actually useful only with LIMIT. Example:

Delete from somelog
WHERE user = 'jcol'
Order by timestamp
LIMIT 1

This will delete record rows that match the WHERE clause and are first inserted (determined by timestamp.

The DELETE statement's LIMIT rows option is unique to MySQL and tells the server the maximum number of record rows that can be deleted before the control is returned to the client. This can be used to ensure that a specific DELETE command does not take too long. You can simply repeat the DELETE command until the number of affected record lines is less than the LIMIT value.

From MySQL 4.0, you can specify multiple tables in the DELETE statement to DELETE record rows that depend on special situations in multiple tables from one table. However, order by or LIMIT cannot be used to delete multiple tables.

MySQL Stored Procedure for table splitting

In-depth discussion on MySQL Lock Mechanism

Detailed description of MySQL Data Table types

Enumeration in the MySQL Field

Add and delete fields in MySQL