php - 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' 进行更新

我究竟做错了什么？

标签： phpmysql

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
)