Mysql multi-table join update/delete SQL statement

We will use update and delete to implement Association deletion and association update in mysql. The following is an example of mysql multi-Table Association update/deletion SQL statements, I hope this method will be helpful to you.

1. Use aliases in mysql multi-table join delete. tblwenhq is the real table name, a is the alias of tblwenhq, and B is the name of another table.

The Code is as follows:	Copy code
DELETE a FROM tblwenhq a, B where a. id = B. id

2. When using mysql to perform the delete from operation, if the FROM Statement of the subquery and the update/delete object use the same table, an error will occur.

Delete from tab1 WHERE col1 = (select max (col1) FROM tab1 );

ERROR 1093 (HY000): You can't specify target table 'tab1' for update in FROM clause

Correct usage: delete from tab1 WHERE col1 = (select max (col1) FROM tab1 as );

Table associated Update notes

The Code is as follows:	Copy code
UPDATE B, a SET B. public = a. public WHERE B. id = a. id

Example

Update table_name set col1 = xx, col2 = yy where col = zz. Sometimes updating a table may involve multiple data tables, for example:

The Code is as follows:	Copy code
Update table_1 set score = score + 5 where uid in (select uid from table_2 where sid = 10 );

In fact, update can also use left join and inner join for association, which may be more efficient. Replace the preceding SQL statement
The join method is as follows:

The Code is as follows:	Copy code
Update table_1 t1 inner join table_2 t2 on t1.uid = t2.uid set score = score + 5 where t2.sid = 10;

Example 1

MySQL multi-Table associated data is deleted at the same time

Category and news ).

The id (column number) Field in category is used as the primary key of the table. It uniquely identifies the information of a column.
The id field in news is used as the primary key of the table. It uniquely identifies the information of a column.

The category_id (column number) field is associated with the id field of the category table.

1. SQL deletion statement

The Code is as follows:	Copy code
Delete category, news from category left join news on category. id = news. category_id