Delete multiple table data and delete associated data in MySQL

Source: Internet
Author: User

There are many ways to delete data in MySQL, the most common is to use Delete to delete the records, let me introduce the delete delete a single record and delete the multi-table associated data of some simple instances.

1. Delete from T1 where condition

2. Delete t1 from T1 where condition

3. Delete T1 from T1,t2 where condition

4. Delete t1,t2 from t1,t2 where condition

The first 3 are feasible, and the 4th is not.

That is, you cannot do multiple table delete data operations by simply using the DELETE statement, but you can establish cascade deletions and cascade between two tables

Delete a relationship, you can implement deleting data from one table while deleting related data from another table.

1, from the data table t1 those ID values in the data table T2 matching records are all deleted

The code is as follows Copy Code

Delete T1 from t1,t2 where t1.id=t2.id or delete from T1 USING t1,t2 where t1.id=t2.id

2, from the data table T1 in the data table T2 no matching records to find out and delete

The code is as follows Copy Code

DELETE T1 from T1 the left JOIN T2 on t1.id=t2.id WHERE t2.id is NULL or

DELETE from t1,using T1 left JOIN T2 on t1.id=t2.id WHERE t2.id is NULL

3. Find the same record data from the two tables and delete the data from the two tables

The code is as follows Copy Code

DELETE t1,t2 from T1 left JOIN T2 on T1.id=t2.id WHERE t1.id=25

Note that the t1,t2 in the delete t1,t2 from here cannot be an alias

Such as:

The code is as follows Copy Code

Delete T1,T2 from table_name as T1 left join Table2_name as T2 on T1.id=t2.id where

Table_name.id=25

Execution in the data is wrong (MYSQL version is not less than 5.0 in 5.0 is possible)

The above statement is rewritten as

The code is as follows Copy Code

Delete Table_name,table2_name from table_name as T1 left joins Table2_name as T2 on

T1.id=t2.id where table_name.id=25

Execution in the data is wrong (MYSQL version less than 5.0 is possible in 5.0

Delete How to remove associated data for cascading deletions

Create TABLE IF not EXISTS ' dcsmember ' (
' id ' int (3) auto_increment NOT NULL primary key,
' name ' varchar (n) ull,
' password ' varchar (+) NOT NULL,
' PhoneNumber ' char (a),
' time ' DATETIME not null, 
' Jifen ' int ( 8)   NOT NULL default ' $ ',
' email ' varchar (+) NOT NULL,
' power ' int (2) NOT null default ' 1 '
) CHARACTER SET GB2312 ";
Create table  IF not EXISTS ' SP ' (
' spid ' int. ' int (5) auto_increment NOT null primary key,
' Spuserid ' int (3) not NULL,
' spname ' varchar (a) NOT null,
' Spmoney ' float (6) is not NULL,
' spopt ' char (a) NOT null,
' spsay ' varchar
' sptime ' DATE default ' 2008-10-01 ',
' Spendor ' TINYINT (1) Default ' 1 ',
INDEX (' Spuserid ')  
) CHARACTER SET gb2312 ";
 

The code is as follows Copy Code

$sqldel = "DELETE from Dcsmember WHERE email= ' $value '";

Now I use the Membership form to delete the member's information
, you also need to delete the member's information from another table, Dcsmember. ID is external code, corresponding to Sp.spuserid,?
How to Write

The code is as follows Copy Code

Delete D, s from Dcsmember D inner join SP s on d.id = S.spuserid where d.email= "xxxxxx"

Delete multiple table data and delete associated data in MySQL

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.