MySQL Multiple Table association update/DELETE SQL statement

1, MySQL multiple table association Delete use alias, TBLWENHQ is the real table name, A is TBLWENHQ alias, B is another table name

The code is as follows	Copy Code
DELETE A from TBLWENHQ a,b where a.id=b.id

2. When using MySQL for the delete from operation, an error occurs if the FROM clause of the subquery and the update/delete object use the same table.

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 a);

Table Associated Update Notes

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

Cases

Perform updates on a single table nothing more to say than to update table_name set col1 = Xx,col2 = yy where col = zz, which is mainly the setting of the Where condition. Sometimes updating a table may involve more than one datasheet, 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 be used to the left join, inner join to the association, may be more efficient execution, the above SQL to replace the
Join in the following ways:

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 Multiple table association data deletion at the same time

Category (Column information table) and news (press data sheet).

The ID (column number) field in the category is used as the primary key (primary key) for the table. The only information that identifies a column.
The ID field in news is the primary key (primary key) for the table. The only information that identifies a column.

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

1.SQL DELETE statement

code is as follows	copy code
Delete Category,news from category left JOIN news on category.id = news.category_id