MySQL学习笔记（十一）多表删除

1. 多表删除

DELETE tbl_name[.*] [,tbl_name[.*]] ... FROM table_references [WHERE where_condition]

示例：

//查找商品表中重复的记录，即统计记录中goods_name的个数是否超过1.

SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING COUNT(goods_name) >=2 ORDER BY goods_id;

//删除记录中id较大的重复记录

DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING COUNT(goods_name) >= 2 ORDER BY goods_id) AS t2  ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2. goods_id;

可以看到记录中重复的22、23被删除。