У меня есть таблица с тремя столбцами. Нет первичного ключа, поэтому могут быть повторяющиеся строки. Мне нужно просто сохранить его и удалить остальных. Любая идея, как это сделать, это Sql Server?
Я бы выбрал строку DISTINCT и бросил их во временную таблицу, затем отменил исходную таблицу и скопировал данные из temp. EDIT: теперь с фрагментом кода!
INSERT INTO TABLE_2
SELECT DISTINCT * FROM TABLE_1
GO
DELETE FROM TABLE_1
GO
INSERT INTO TABLE_1
SELECT * FROM TABLE_2
GO
Добавьте столбец идентификатора, чтобы действовать как суррогатный первичный ключ, и используйте его для идентификации двух из трех строк, которые нужно удалить.
Я бы подумал о том, чтобы оставить столбец идентификатора на месте после этого, или если это какая-то таблица ссылок, создайте составной первичный ключ в других столбцах.
Следующий пример также работает, когда ваш ПК является всего лишь подмножеством всех столбцов таблицы.
(Примечание: мне нравится подход с добавлением другого столбца с суррогатным идентификатором больше. Но, возможно, это решение также удобно.)
Сначала найдите повторяющиеся строки:
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
Если их немного, вы можете удалить их вручную:
set rowcount 1
delete from t1
where col1=1 and col2=1
Значение "rowcount" должно быть в n-1 раз больше количества дубликатов. В этом примере есть 2 дубликаты, поэтому rowcount равно 1. Если вы получаете несколько повторяющихся строк, вы должны сделать это для каждого уникального первичного ключа.
Если у вас много дубликатов, скопируйте каждый ключ один раз в таблицу anoher:
SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
Затем скопируйте ключи, но устраните дубликаты.
SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2
В ваших ключах теперь есть уникальные ключи. Проверьте, нет ли результата:
SELECT col1, col2, count(*)
FROM holddups
GROUP BY col1, col2
Удалить дубликаты из исходной таблицы:
DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2
Вставить исходные строки:
INSERT t1 SELECT * FROM holddups
btw и для полноты: в Oracle есть скрытое поле, которое вы можете использовать (rowid):
DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;
см. ниже: Сайт знаний Microsoft
Здесь метод, который я использовал когда я задал этот вопрос -
DELETE MyTable
FROM MyTable
LEFT OUTER JOIN (
SELECT MIN(RowId) as RowId, Col1, Col2, Col3
FROM MyTable
GROUP BY Col1, Col2, Col3
) as KeepRows ON
MyTable.RowId = KeepRows.RowId
WHERE
KeepRows.RowId IS NULL
Это способ сделать это с помощью Common Table Expressions, CTE. Он не содержит циклов, никаких новых столбцов или чего-либо еще и не вызывает никаких нежелательных триггеров (из-за удаления + вставки).
Вдохновленный в этой статье.
CREATE TABLE #temp (i INT)
INSERT INTO #temp VALUES (1)
INSERT INTO #temp VALUES (1)
INSERT INTO #temp VALUES (2)
INSERT INTO #temp VALUES (3)
INSERT INTO #temp VALUES (3)
INSERT INTO #temp VALUES (4)
SELECT * FROM #temp
;
WITH [#temp+rowid] AS
(SELECT ROW_NUMBER() OVER (ORDER BY i ASC) AS ROWID, * FROM #temp)
DELETE FROM [#temp+rowid] WHERE rowid IN
(SELECT MIN(rowid) FROM [#temp+rowid] GROUP BY i HAVING COUNT(*) > 1)
SELECT * FROM #temp
DROP TABLE #temp
Это сложная ситуация. Не зная о вашей конкретной ситуации (размер таблицы и т.д.), я думаю, что ваш лучший снимок - это добавить столбец идентификации, заполнить его, а затем удалить в соответствии с ним. Вы можете удалить столбец позже, но я бы предположил, что вы должны сохранить его, так как это действительно хорошо в таблице
После очистки текущего беспорядка вы можете добавить первичный ключ, который включает все поля в таблице. это заставит вас снова войти в беспорядок. Конечно, это решение может очень сильно нарушить существующий код. Это также нужно будет обработать.
Можете ли вы добавить поле идентификации первичного ключа в таблицу?
Manrico Corazzi - я специализируюсь на Oracle, а не на MS SQL, поэтому вам нужно сказать мне, возможно ли это в качестве повышения производительности: -
Здесь другой способ, с тестовыми данными
create table #table1 (colWithDupes1 int, colWithDupes2 int)
insert into #table1
(colWithDupes1, colWithDupes2)
Select 1, 2 union all
Select 1, 2 union all
Select 2, 2 union all
Select 3, 4 union all
Select 3, 4 union all
Select 3, 4 union all
Select 4, 2 union all
Select 4, 2
select * from #table1
set rowcount 1
select 1
while @@rowcount > 0
delete #table1 where 1 < (select count(*) from #table1 a2
where #table1.colWithDupes1 = a2.colWithDupes1
and #table1.colWithDupes2 = a2.colWithDupes2
)
set rowcount 0
select * from #table1
Как насчет:
select distinct * into #t from duplicates_tbl
truncate duplicates_tbl
insert duplicates_tbl select * from #t
drop table #t
Как насчет этого решения:
Сначала вы выполните следующий запрос:
select 'set rowcount ' + convert(varchar,COUNT(*)-1) + ' delete from MyTable where field=''' + field +'''' + ' set rowcount 0' from mytable group by field having COUNT(*)>1
И тогда вам просто нужно выполнить возвращенный набор результатов
set rowcount 3 delete from Mytable where field='foo' set rowcount 0
....
....
set rowcount 5 delete from Mytable where field='bar' set rowcount 0
Я обработал случай, когда у вас есть только один столбец, но довольно легко адаптировать один и тот же подход более чем к одному столбцу. Дайте мне знать, если вы хотите, чтобы я опубликовал код.
Я не уверен, что это работает с операторами DELETE, но это способ найти повторяющиеся строки:
SELECT *
FROM myTable t1, myTable t2
WHERE t1.field = t2.field AND t1.id > t2.id
Я не уверен, что вы можете просто изменить "SELECT" на "DELETE" (кто-нибудь хочет сообщить мне?), но даже если вы не можете, вы можете просто сделать его в подзапрос.