Mysql batch update, multi-table update, and multi-table delete _ MySQL

Mysql batch update, multi-table update, multi-table deletion bitsCN.com This article introduces some instances in mysql for batch update, multi-table update, and multi-table deletion, for more information, see.

The main content of this section:
Mysql batch update, multi-table update, and multi-table deletion

1. batch update:

Sample Code: update tepoi, pinf set tepoi. x = pinf. fx, tepoi. y = pinf. fy
Where tepoi. pid = pinf. dmgis_id and tepoi. pid & gt; 10000;

Assume that tables a and B are in the following structure:

Code example: a (id, email, name) B (id, email)
Update a inner join B on a. id = B. id set
Update tableA inner join tableB on tableA. FieldA = tableB. FieldB (inner join is used in update)
Set
TableA. field = value,
TableB. field = value

II. update multiple tables
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:

Code example: 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.

3. 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.
To delete record rows from multiple tables at the same time, other tables can also be used for retrieval.

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

Code example: 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

The above only deletes matching 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:

Code example: delete from somelog
WHERE user = 'jcol'
Order by timestamp
LIMIT 1

Records that match the WHERE clause will be deleted 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 use the DELETE command again until the number of affected record lines is less than the LIMIT value.

Note:
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.
The original link: http://www.jbxue.com/db/13130.html

BitsCN.com