MySQL multi-Table connection Deletion

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.