MySQL multi-Table connection Deletion
DELETE is a very careful SQL operation. In general, DELETE operations must be careful to avoid unnecessary losses.
DELETE has the following situations:
ØDelete from t1 where Condition
ØDelete t1 from t1 where Condition
ØDelete t1 from t1, t2 where Condition
ØDelete t1, t2 from t1, t2 where conditions
Simply put, the delete statement cannot delete data from multiple tables. However, you can create a cascade deletion relationship between two tables, when you delete data from a table, you can also delete data from another table.
Example:
1) Delete all matching records with id values in the data table Track from the data table MV.
DELETE music
From mv, Track
WHERE Track. trkid = MV. mvid
Equivalent
DELETE FROM MV
Using mv, Track
WHERE Track. trkid = MV. mvid
2) Find and delete no matching records in the data table Track from the data table MV.
DELETE music
FROM MV
Left join Track
On mv. mvid = Track. trkid
WHERE Track. trkid IS NULL
Equivalent
DELETE FROM MV
USING MV
Left join Track
On mv. mvid = Track. trkid
WHERE Track. trkid IS NULL
3) Find the data of the same record from the two tables andBoth tables are deleted.
DELETE music, Track
FROM MV
Left join Track
On mv. mvid = Track. trkid
Where mv. mvid = 1
Alias:
DELETE t1, t2
From mv t1
Left join Track t2
ON t1.mvid = t2.trkid
WHERE t1.mvid = 2
※In either of the following situations, an error is reported:
Delete music, Track
From MV as t1
Left join Track as t2
On t1.mvid = t2.trkid
Where t1.mvid = 5
***********************
DELETE t1, t2
From mv t1
Left join Track t2
ON t1.mvid = t2.trkid
Where mv. mvid = 2
So if the alias method is used, both the upper and lower aliases are used.
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.