MySQL Associated Delete

Reference URL: http://www.111cn.net/database/mysql/51146.htm

The original page ads too much, I copied the next.

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
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
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
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:
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
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 is less than 5.0 in 5.0 is possible)
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 (NOT NULL),
' Password ' varchar (+) NOT NULL,
' PhoneNumber ' char (20),
' Time ' DATETIME is not NULL,
' Jifen ' int (8) NOT null DEFAULT ' 20 ',
' Email ' varchar (+) not NULL,
' Power ' int (2) not NULL DEFAULT ' 1 '
) CHARACTER SET gb2312 ";
Create TABLE IF not EXISTS ' SP ' (
' spid ' int (5) auto_increment NOT null primary key,
' Spuserid ' int (3) is not NULL,
' spname ' varchar (NOT NULL),
' Spmoney ' float (6) NOT NULL,
' Spopt ' char (not NULL),
' Spsay ' varchar (50),
' SpTime ' DATE DEFAULT ' 2008-10-01 ',
' Spendor ' TINYINT (1) DEFAULT ' 1 ',
INDEX (' Spuserid ')
) CHARACTER SET gb2312 ";

$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
Delete D, s from Dcsmember D inner join SP s on d.id = S.spuserid where d.email= "xxxxxx"

Here are some of your own practices:

Delete the T1 table, according to T2 conditions, the T2 table is also restricted by the T3 table. The logic is as follows:

DELETE T1
From
T_sales_query as T1
JOIN t_sales_payment as t2 on t1.id = t2.sales_id
Join t_sales_activity as T3 on T2. Activity_code=t3. Activity_code
WHERE
T3. Status= ' and T3. Sales_time_end between "and"

MySQL Associated Delete