Delete multiple table data and delete associated data in MySQL

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