In the syntax of MySQL. You cannot call the result set of a subquery directly when you modify or delete it. The child must be queried for its alias first. In the call.
For example: The following removal of duplicates
DELETE From T_equipment_type t WHERE t.type = 2
and (T.type_code,t.type_name) in (
SELECT Type_code,type_name from T_equipment_type
GROUP by Type_code, Type_name has COUNT (*) > 1)
and t.rec_id not in (
SELECT MIN (rec_id) from T_equipment_type
GROUP by Type_code, Type_name has COUNT (*) > 1)
This will cause an error.
DELETE From T_equipment_type WHERE rec_id in (
SELECT b.* from (
SELECT t.rec_id from T_equipment_type t WHERE t.type = 2
(T.type_code,t.type_name) In (
SELECT Type_code,type_name from T_equipment_type
GROUP by Type_code have COUNT (*) > 1)
and t.rec_id not in (
SELECT MIN (rec_id) rec_id from T_equipment_type
GROUP by Type_code,type_name have COUNT (*) > 1)
) b
)
A pit of MySQL