如何在 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
) 

在DB Fiddle上查看

Answer 2

如果要删除除第一行以外的所有行或行的任何小子集，这里有一个更快的方法：

CREATE TABLE new LIKE real;
INSERT INTO new
SELECT * FROM real WHERE ...;   -- see below
RENAME TABLE real TO old,
new TO real;
DROP TABLE old;

在您的情况下：

SELECT * FROM real ORDER BY id LIMIT 1;