Блог

У меня есть запрос для проверки дубликатов из таблицы:-

 (SELECT assignment_name 
 FROM   (SELECT xx.supervisor_stg.*, 
                Row_number() 
                  over ( 
                    PARTITION BY assignment_name 
                    ORDER BY assignment_name) AS c 
         FROM   xx.supervisor_stg) 
 WHERE  c > 1) 
  

Я хочу удалить повторяющиеся данные из этих данных, но так, чтобы, если одно значение, например, assignment_name ‘Smith’, записывалось трижды, тогда 1 значение должно остаться, а остальные 2 должны быть удалены.

Когда я использую следующий запрос, я получаю сообщение об ошибке :

 Delete From supervisor_stg
where asg_name <>(SELECT Asg_Name 
 FROM   (SELECT xx.supervisor_stg.*, 
                Row_number() 
                  over ( 
                    PARTITION BY assignment_name 
                    ORDER BY assignment_name) AS c 
         FROM   xx.supervisor_stg) 
 WHERE  c > 1) 


SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:    
*Action:
  

В Oracle вы можете удалять из подзапроса, например delete from (select * from emp where empno < 10) . Ну, пример глупый, но вы поняли идею. В вашем случае это будет:

 delete from
(
  select *
  from
  (
    select 
      s.*, 
      row_number() over (partition by assignment_name order by assignment_name) as rn
    from xx.supervisor_stg s
  )
  where rn > 1
);
  

Хотя это должно работать, может случиться так, что Oracle по какой-то причине не исключает это. Обновление: я пробовал это в Oracle 11.2, и Oracle выдает мне ORA-01752: «невозможно удалить из представления без сохранения ровно одной таблицы с сохранением ключа». Поэтому, хотя мы выбираем только из одной таблицы, Oracle как-то запутывается. Читайте дальше и выбирайте другое решение 🙂

Альтернативой может быть удаление каждой записи, для которой не существует другой записи с тем же именем присваивания_имя и более низким (или более высоким) идентификатором строки.

 delete from xx.supervisor_stg s
where not exists
(
  select *
  from xx.supervisor_stg other
  where other.assignment_name = s.assignment_name
  and other.rowid < s.rowid
);
  

Вместо коррелированного подзапроса with NOT EXISTS вы можете использовать некоррелированный подзапрос with NOT IN , где у вас есть все минимальные (или максимальные) идентификаторы строк в наборе, а все остальные вы удаляете.

 delete from xx.supervisor_stg
where rowid not in
(
  select min(rowid)
  from xx.supervisor_stg
  group by assignment_name
);
  

Что касается вашего собственного оператора delete:

• Ваш подзапрос не связан с вашим основным запросом, поэтому вы получаете много записей. <> NOT IN По этой причине вам придется заменить на.
• <> не имеет смысла в сочетании с WHERE c > 1 , потому что последнее дает вам записи для удаления, а не для сохранения. Либо сделайте это IN (а не NOT IN ), либо сделайте это WHERE c = 1 .
• Но тогда: вы сравниваете только asg_name. Но asg_name одинаково для записей, которые нужно удалить, и записей, которые нужно сохранить. Вам придется использовать что-то, что различает записи. Rowid идеально подходит для этого.

Вы получите:

 Delete From supervisor_stg
Where rowid In
(
  SELECT rowid 
  FROM 
  (
    SELECT 
      xx.supervisor_stg.*, 
      Row_number() over (PARTITION BY assignment_name ORDER BY assignment_name) AS c 
    FROM xx.supervisor_stg
  ) 
  WHERE c > 1
);
  

что слишком сложно. Как показано, вы можете использовать простое агрегирование ( min(rowid) / group by assignment_name ). Вы бы использовали функцию window, например, row_number() чтобы избежать необходимости дважды читать таблицу, как в моем первом запросе. В вашем запросе вы все еще дважды читаете таблицу, один раз, чтобы найти записи для сохранения, а затем еще раз, чтобы найти записи для удаления.

 ;WITH numbered AS (
    SELECT ROW_NUMBER() OVER(PARTITION BY [dupe-column-list] ORDER BY [dupe-column-list]) AS _dupe_num 
    FROM [table-name] 
    WHERE 1=1 -- any where clause if required
)
DELETE FROM numbered WHERE _dupe_num > 1;
  

Этот запрос присвоит вашей таблице номер строки на основе полей [dupe-column-list], которые вы туда добавите. Вы можете указать порядок для этих записей. Оператор delete удалит все записи, отображаемые более одного раза для этого [dupe-column-list] (кроме первого вхождения).

Редактировать: только что заметил, что это oracle. Не уверен, что тогда это сработает для вас. Выше приведен MSSQL.

В Oracle вы можете использовать rowid для этой цели:

 delete from xx.SUPERVISOR_STG
    where rowid <> (select min(s2.rowid)
                    from xx.SUPERVISOR_STG
                    where s2.assignment_name = SUPERVISOR_STG.assignment_name
                   );