一、DELETE删除多表数据,怎么才能同时删除多个关联表的数据呢
1. delete from t1 where 条件
2. delete t1 from t1 where 条件
3. delete t1 from t1,t2 where 条件
4. delete t1,t2 from t1,t2 where 条件 (不行)
简单用delete语句无法进行多表删除数据操作,不过可以建立级联删除,在两个表之间建立级联删除关系,则可以实现删除一个表的数据时,同时删除另一个表中相关的数据。
1、从数据表t1中把那些id值在数据表t2里有匹配的记录全删掉:
DELETE T1 FROM T1,T2 WHERE T1.ID=T2.ID 或 DELETE FROM T1 USING T1,T2 WHERE T1.ID=T2.ID
2、从数据表T1里在数据表T2里没有匹配的记录查询处理并删除掉:
DELETE T1 FROM T1 LEFT JOIN T2 ON T1.ID=T2.ID WHERE T2.ID IS NULL
3、从两个表中找出相同NAME的数据并把两个表中的数据删掉:
DELETE T1,T2 FROM T1 LEFT JOIN T2 ON T1.ID=T2.ID WHERE T1.NAME=T2.NAME
注意此处的DELETE T1,T2 FROM 中的T1和T2不能是别名
如:DELETE T1,T2 FROM TABLE1 LEGT JOIN TABLE2 ON T1.ID=T2.ID WHERE T1.NAME=T2.NAME 在数据里面执行是错误的(MYSQL 版本不小于5.0在5.0中是可以的) 上述语句改 写成 DELETE TABLE1,TABLE2 FROM TABLE1 LEFT JOIN TABLE2 ON T1.ID=T2.ID WHERE T1.NAME=T2.NAME