Mysql查询选择后删除记录..? [英] Mysql query to Delete records after select..?

问题描述

选择记录后，我想删除该记录..我尝试了以下查询 - (删除 ,MAX(value) 后，但它的显示 - 您不能指定目标表test"进行更新在 FROM 子句中

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)
);

推荐答案

你可以试试下面的 - 你需要使用子查询进行内部选择语句

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查询选择后删除记录..?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！