Mysql query to Delete records after select..?

Question

after selecting records i want to delete that records..i tried following query - (after removing ,MAX(value) but its showing- You can't specify target table 'test' for update in FROM clause

DELETE FROM test WHERE ext_no IN 
(
SELECT   ext_no, MAX(value)
FROM     test
GROUP BY ext_no
HAVING   COUNT(*) > 2 AND 
         COUNT(*) = COUNT(CASE value WHEN 0 THEN 1 END)
);

I don't get the logic - you appear to want to delete all ext_nos where there are more than 2 records for that ext_no AND ALL records for that ext_no have a value of 0. These 2 conditions seem incompatible can you add sample data and expected outcome as text to the question please. — P.Salmon, Nov 19 '18 at 08:07
@P.Salmon yes sure.. you can refer my previous question.. for sample data [here](https://stackoverflow.com/questions/52840268/mysql-query-to-select-distinct-records-on-conditions) — Sachin_Bhagat_Ssbits, Nov 19 '18 at 08:12

score 1 · Accepted Answer · answered Nov 19 '18 at 08:07

You can try below - you need to use subquery for inner select statement

DELETE FROM test WHERE ext_no IN 
(
select ext_no from
(
SELECT   ext_no, MAX(value)
FROM     test
GROUP BY ext_no
HAVING   COUNT(*) > 2 AND 
         COUNT(*) = COUNT(CASE value WHEN 0 THEN 1 END)
)t)

Mysql query to Delete records after select..?

1 Answers1

Linked