在mysql的語法中。修改或者刪除的時候不能直接調用子查詢的結果集。需要先給子查詢其別名。在調用。 比如：下麵的去除重覆項 DELETE FROM t_equipment_type t WHERE t.type = 2 AND (t.type_code,t.type_name) IN ( SELEC ...
在mysql的語法中。修改或者刪除的時候不能直接調用子查詢的結果集。需要先給子查詢其別名。在調用。
比如：下麵的去除重覆項
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 HAVING COUNT(*) > 1)
AND t.rec_id NOT IN (
SELECT MIN(rec_id) FROM t_equipment_type
GROUP BY type_code, type_name HAVING COUNT(*) > 1)
這樣會報錯。
正確的寫法如下：
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 HAVING COUNT(*) > 1)
AND t.rec_id NOT IN (
SELECT MIN(rec_id) rec_id FROM t_equipment_type
GROUP BY type_code,type_name HAVING COUNT(*) > 1)
) b
)
