Удаление дублирующегося SQL записывает для разрешения уникального ключа
У меня есть таблица ('продажи') в MySQL DB, которому нужно было законно осуществить ограничение на уникальность данных для предотвращения дубликатов. Сначала удалить простофиль и установить ограничение оказываются немного хитрыми.
Структура таблицы (упростила):
- 'идентификатор (уникальный, autoinc)'
- product_id
Цель состоит в том, чтобы осуществить уникальность для product_id. Политика дедупликации, которую я хочу применить, состоит в том, чтобы удалить все дублирующиеся записи кроме последний раз созданный, например: самый высокий идентификатор.
Или помещать иначе, я хотел бы удалить только дублирующиеся записи, исключая идентификаторы, подобранные следующим запросом, также сохраняя существующие необманутые записи:
select id
from sales s
inner join (select product_id,
max(id) as maxId
from sales
group by product_id
having count(product_id) > 1) groupedByProdId on s.product_id
and s.id = groupedByProdId.maxId
Я боролся с этим на двух передних сторонах - запись запроса для выбора корректных записей для удаления и затем также ограничение в MySQL, где подвыбор ИЗ пункта УДАЛЕНИЯ не может сослаться на ту же таблицу, из которой удаляются данные.
Я проверил этот ответ, и это, казалось, имело дело с предметом, но казаться характерным для SQL-сервера, хотя я не буду управлять этим вопросом из дублирования другого.
4 ответа
В ответ на ваш комментарий, вот запрос, который работает в MySQL:
delete YourTable
from YourTable
inner join YourTable yt2
on YourTable.product_id = yt2.product_id
and YourTable.id < yt2.id
Это удалит только дубликаты строк. внутреннее объединение отфильтрует последнюю строку для каждого продукта, даже если других строк для того же продукта не существует.
P.S. Если вы попытаетесь дать псевдоним таблице после FROM, MySQL требует указать имя базы данных, например:
delete <DatabaseName>.yt
from YourTable yt
inner join YourTable yt2
on yt.product_id = yt2.product_id
and yt.id < yt2.id;
Я мог бы сделать следующее в sql-server, чтобы удалить дубликаты:
DELETE FROM Sales
FROM Sales
INNER JOIN Sales b ON Sales.product_id = b.product_id AND Sales.id < b.id
Похоже, аналогичный оператор delete для mysql может быть таким:
DELETE FROM Sales
USING Sales
INNER JOIN Sales b ON Sales.product_id = b.product_id AND Sales.id < b.id
Возможно, использовать ALTER IGNORE TABLE ... ДОБАВИТЬ УНИКАЛЬНЫЙ КЛЮЧ .
Например:
describe sales;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| product_id | int(11) | NO | | NULL | |
+------------+---------+------+-----+---------+----------------+
select * from sales;
+----+------------+
| id | product_id |
+----+------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
| 5 | 3 |
| 6 | 2 |
+----+------------+
ALTER IGNORE TABLE sales ADD UNIQUE KEY idx1(product_id), ORDER BY id DESC;
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 3 Warnings: 0
select * from sales;
+----+------------+
| id | product_id |
+----+------------+
| 6 | 2 |
| 5 | 3 |
| 2 | 1 |
+----+------------+
См. Этот пифийский пост для получения дополнительной информации.
Обратите внимание, что id заканчиваются в обратном порядке. Я не думаю, что это имеет значение, поскольку порядок id s не должен иметь значения в базе данных (насколько мне известно!). Однако, если это вас не устраивает, сообщение, указанное выше, также показывает способ решения этой проблемы. Однако он включает в себя создание временной таблицы, которая требует больше места на жестком диске, чем метод на месте, который я опубликовал выше.
Этот тип проблемы легче решить с помощью CTE и функций ранжирования, однако вы должны иметь возможность сделать что-то вроде следующего, чтобы решить вашу проблему:
Delete Sales
Where Exists(
Select 1
From Sales As S2
Where S2.product_id = Sales.product_id
And S2.id > Sales.Id
Having Count(*) > 0
)