删除具有特定计数的mysql相关记录

Question

我需要删除2012年之前创建的表'people'中属于酒店标识为'1'的记录，并且在相关表'taskinstance'中只有1条记录。这些表格以这种方式关联：people.id = taskinstance.idClient。我想出了以下这给我一个语法错误。

DELETE people FROM taskinstance people 
INNER JOIN 
    (SELECT * 
    FROM taskinstance 
    WHERE substring(people.dateCreated,1,4) < 2012 and people.idHotel = '1' 
    GROUP BY taskinstance.idClient 
    HAVING COUNT(*) = 1) taskinstance 
ON people.id = taskinstance.idClient 

如果我只是想查看记录，以下工作：

SELECT * 
FROM people 
INNER JOIN taskinstance ON people.id = taskinstance.idClient 
WHERE substring(people.dateCreated,1,4) < 2012 and people.idHotel = '1' 
GROUP BY people.id 
HAVING COUNT(1) = 1 

感谢所有帮助你可以给。

Answer 1

尝试这样：

DELETE FROM people WHERE people.id IN 
(SELECT people.id 
FROM people 
INNER JOIN taskinstance ON people.id = taskinstance.idClient 
WHERE substring(people.dateCreated,1,4) < 2012 and people.idHotel = '1' 
GROUP BY people.id 
HAVING COUNT(*) = 1) 

编辑：在我自己的数据库进行测试，这一次被拒绝了具有WHERE子句在同一个表的DELETE子句。

或者这样：

DELETE people FROM people 
INNER JOIN (
    SELECT people.id 
    FROM people 
    INNER JOIN taskinstance ON people.id = taskinstance.idClient 
    WHERE substring(people.dateCreated,1,4) < 2012 and people.idHotel = '1' 
    GROUP BY taskinstance.idClient 
    HAVING COUNT(*) = 1 
    ) p 
ON p.id = people.id 

编辑：我测试了几乎相同的查询，这一个在我自己的数据库（只是我自己的表），并且有在查询中没有语法错误。

请发表您的语法错误的全文，以便我们可以追踪到该问题，特别是它所说的部分，"... for the right syntax to use near 'something something something' on line X."something something部分是故障排除最重要的部分。例如，如果它说"near '' on line 6"那么你几乎肯定不会逃过你的单引号1.