mysql - 如何在 SQL 中删除多个 MySql Apache 表行？

Question

我正在使用最新版本的 XAMPP 2018 以及 MariaDB 和 Apache。

我正在执行这个sql:

 DELETE FROM customer_info 
 WHERE id NOT IN (SELECT MIN(id) FROM `customer_info` GROUP BY `paypal_id`) ;

并收到此错误

 Error
 SQL query: 

 DELETE FROM customer_info WHERE id NOT IN (SELECT MIN(id) FROM `customer_info` GROUP BY `paypal_id`)
 MySQL said: Documentation

 #1093 - Table 'customer_info' is specified twice, both as a target for 'DELETE' and as a separate source for data

我尝试了不同的方法来删除重复的 paypal_id 条目，包括使用 COUNT(*) > 1，它也会返回错误。

Answer 1

MySQL 不允许您在同一个表中进行 DELETE 和 SELECT。

您可以尝试在NOT IN中使用子查询。

DELETE FROM customer_info 
 WHERE id NOT IN (
   SELECT ID FROM (
     SELECT MIN(id) Id
     FROM `customer_info` 
     GROUP BY `paypal_id`
   ) t1
 ) 

View on DB Fiddle