MySQL不允许我删除记录

Question

我正在尝试从PHP执行此MySQL子句$conn = new mysqli($servername, $username, $password, $dbname);$sql = "DELETE FROM table_2 WHERE id in (select DISTINCT(id) FROM table_2 WHERE id NOT IN (SELECT id FROM table_2 WHERE p = 1 AND r =1))";if ($conn->query($sql) === TRUE) {    echo "Record deleted successfully";} else {    echo "Error deleting record: " . $conn->error;}我收到以下错误删除记录时出错：您无法在FROM子句中指定目标表“ table_2”进行更新我究竟做错了什么？

Answer 1

您可以使用子查询来绕过MySQL的这一限制，但是您的语句可以像这样简化：

DELETE FROM table_2 

WHERE id NOT IN (

  SELECT id FROM (

    SELECT id FROM table_2 WHERE p = 1 AND r = 1

  ) t

)