удаление по rowid oracle (14)
Я тестирую что-то в Oracle и заполняю таблицу некоторыми примерами данных, но в процессе я случайно загружал дубликаты записей, поэтому теперь я не могу создать первичный ключ, используя некоторые из столбцов.
Как удалить все повторяющиеся строки и оставить только один из них?
Решение 1)
delete from emp where rowid not in (select max(rowid) from emp group by empno);Решение 2)
delete from emp where rowid in (select rid from (select rowid rid, row_number() over(partition by empno order by empno) rn from emp) where rn > 1);Решение 3)
Для лучшей производительности, вот что я написал:
(см. план выполнения)
DELETE FROM your_table WHERE rowid IN (select t1.rowid from your_table t1 LEFT OUTER JOIN (SELECT MIN(rowid) as rowid, column1,column2, column3 FROM your_table GROUP BY column1, column2, column3) co1 ON (t1.rowid = co1.rowid) WHERE co1.rowid IS NULL);
Проверьте ниже скрипты -
Create table test(id int,sal int);
Insert into test values(1,100); insert into test values(1,100); insert into test values(2,200); insert into test values(2,200); insert into test values(3,300); insert into test values(3,300); commit;
Select * from test;
Вы увидите здесь 6 записей.
4.run ниже запроса -
Delete from test where rowid in (select rowid from (select rowid, row_number() over (partition by id order by sal) dup from test) where dup > 1)
- select * from test;
Вы увидите, что дубликаты записей были удалены.
Надеюсь, это решает ваш запрос. Благодаря:)
Чтобы выбрать дубликаты, только формат запроса может быть:
SELECT GroupFunction(column1), GroupFunction(column2),..., COUNT(column1), column1, column2... FROM our_table GROUP BY column1, column2, column3... HAVING COUNT(column1) > 1
Таким образом, правильный запрос по другому предложению:
DELETE FROM tablename a WHERE a.ROWID > ANY (SELECT b.ROWID FROM tablename b WHERE a.fieldname = b.fieldname AND a.fieldname2 = b.fieldname2 AND ....so on.. to identify the duplicate rows....)
Этот запрос сохранит самую старую запись в базе данных для критериев, выбранных в WHERE CLAUSE .
Oracle Certified Associate (2008)
Я не видел ответов, которые используют общие выражения таблиц и функции окна. Это то, с чем мне легче всего работать.
DELETE FROM YourTable WHERE ROWID IN (WITH Duplicates AS (SELECT ROWID RID, ROW_NUMBER() OVER(PARTITION BY First_Name, Last_Name, Birth_Date) AS RN SUM(1) OVER(PARTITION BY First_Name, Last_Name, Birth_Date ORDER BY ROWID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS CNT FROM YourTable WHERE Load_Date IS NULL) SELECT RID FROM duplicates WHERE RN > 1);
Что следует отметить:
1) Мы проверяем только дублирование полей в разделе.
2) Если у вас есть причина выбрать один дубликат над другими, вы можете использовать предложение order by, чтобы эта строка имела row_number () = 1
3) Вы можете изменить дубликат числа, сохраненный, изменив окончательное предложение where на «Where RN> N» с N> = 1 (я думал, что N = 0 удалит все строки с дубликатами, но просто удалит все строки) ,
4) Добавлено поле раздела Sum, запрос CTE, который будет помечать каждый fow номерами строк в группе. Поэтому для выбора строк с дубликатами, включая первый элемент, используйте «WHERE cnt> 1».
1. решение
Delete from emp where rowid not in (select max(rowid) from emp group by empno);
2. slution
Delete from emp where rowid in (select rid from (select rowid rid, row_number() over(partition by empno order by empno) rn from emp) where rn > 1);
3.solution
Delete from emp e1 where rowid not in (select max(rowid) from emp e2 where e1.empno = e2.empno);
4. решение
Delete from emp where rowid in (select rid from (select rowid rid, dense_rank() over(partition by empno order by rowid) rn from emp) where rn > 1);
Create or replace procedure delete_duplicate_enq as cursor c1 is select * from enquiry; begin for z in c1 loop delete enquiry where enquiry.enquiryno = z.enquiryno and rowid > any (select rowid from enquiry where enquiry.enquiryno = z.enquiryno); end loop; end delete_duplicate_enq;
Create table abcd(id number(10),name varchar2(20)) insert into abcd values(1,"abc") insert into abcd values(2,"pqr") insert into abcd values(3,"xyz") insert into abcd values(1,"abc") insert into abcd values(2,"pqr") insert into abcd values(3,"xyz") select * from abcd id Name 1 abc 2 pqr 3 xyz 1 abc 2 pqr 3 xyz Delete Duplicate record but keep Distinct Record in table DELETE FROM abcd a WHERE ROWID > (SELECT MIN(ROWID) FROM abcd b WHERE b.id=a.id); run the above query 3 rows delete select * from abcd id Name 1 abc 2 pqr 3 xyz
Удаление записей
Для удаления записей из таблицы применяется оператор DELETE:
DELETE FROM имяТаблицы WHERE условие;
Данный оператор удаляет из указанной таблицы записи (а не отдельные значения столбцов), которые удовлетворяют указанному условию. Условие - это логическое выражение, различные конструкции которого были рассмотрены в предыдущих лабораторных занятиях.
Следующий запрос удаляет записи из таблицы Customer, в которой значение столбца LName равно "Иванов":
DELETE FROM Customer
WHERE LName = "Иванов"
Если таблица содержатся сведения о нескольких клиентах с фамилией Иванов, то все они будут удалены.
В операторе WHERE может находиться подзапрос на выборку данных (оператор SELECT). Подзапросы в операторе DELETE работают точно так же, как и в операторе SELECT. Следующий запрос удаляет всех клиентов из города Москва, при этом уникальный идентификатор города возвращается с помощью подзапроса.
DELETE FROM Customer
WHERE IdCity IN (SELECT IdCity FROM City WHERE CityName = "Москва" )
Transact-SQL расширяет стандартный SQL, позволяя использовать в инструкции DELETE еще одно предложение FROM. Это расширение, в котором задается соединение, может быть использовано вместо вложенного запроса в предложении WHERE для указания удаляемых строк. Оно позволяет задавать данные из второго FROM и удалять соответствующие строки из таблицы в первом предложении FROM. В частности предыдущий запрос может быть переписан следующим образом
DELETE FROM Customer
FROM Customer k INNER JOIN
Операция удаления записей из таблицы является опасной в том смысле, что связана с риском необратимых потерь данных в случае семантических (но не синтаксических) ошибок при формулировке SQL-выражения. Чтобы избежать неприятностей, перед удалением записей рекомендуется сначала выполнить соответствующий запрос на выборку, чтобы просмотреть, какие записи будут удалены. Так, например, перед выполнением рассмотренного ранее запроса на удаление не помешает выполнить соответствующий запрос на выборку.
SELECT *
FROM Customer k INNER JOIN
City c ON k.IdCity = c.IdCity AND c.CityName = "Москва"
Для удаления всех записей из таблицы достаточно использовать оператор DELETE без ключевого слова WHERE. При этом сама таблица со всеми определенными в ней столбцами сохраняется и готова для вставки новых записей. Например, следующий запрос удаляет записи обо всех товарах.
DELETE FROM Product
Задание для самостоятельной работы: Сформулируйте на языке SQL запрос на удаление всех заказов, не имеющих в составе ни одного товара (т. е. все пустые заказы).
Удаление повторяющихся строк из таблицы в Oracle (14)
Решение 1)
delete from emp where rowid not in (select max(rowid) from emp group by empno);Решение 2)
delete from emp where rowid in (select rid from (select rowid rid, row_number() over(partition by empno order by empno) rn from emp) where rn > 1);Решение 3)
Я тестирую что-то в Oracle и заполняю таблицу некоторыми примерами данных, но в процессе я случайно загружал дубликаты записей, поэтому теперь я не могу создать первичный ключ, используя некоторые из столбцов.
Как удалить все повторяющиеся строки и оставить только один из них?
Для лучшей производительности, вот что я написал:
(см. план выполнения)
DELETE FROM your_table WHERE rowid IN (select t1.rowid from your_table t1 LEFT OUTER JOIN (SELECT MIN(rowid) as rowid, column1,column2, column3 FROM your_table GROUP BY column1, column2, column3) co1 ON (t1.rowid = co1.rowid) WHERE co1.rowid IS NULL);
Проверьте ниже скрипты -
Create table test(id int,sal int);
Insert into test values(1,100); insert into test values(1,100); insert into test values(2,200); insert into test values(2,200); insert into test values(3,300); insert into test values(3,300); commit;
Select * from test;
Вы увидите здесь 6 записей.
4.run ниже запроса -
Delete from test where rowid in (select rowid from (select rowid, row_number() over (partition by id order by sal) dup from test) where dup > 1)
- select * from test;
Вы увидите, что дубликаты записей были удалены.
Надеюсь, это решает ваш запрос. Благодаря:)
Чтобы выбрать дубликаты, только формат запроса может быть:
SELECT GroupFunction(column1), GroupFunction(column2),..., COUNT(column1), column1, column2... FROM our_table GROUP BY column1, column2, column3... HAVING COUNT(column1) > 1
Таким образом, правильный запрос по другому предложению:
DELETE FROM tablename a WHERE a.ROWID > ANY (SELECT b.ROWID FROM tablename b WHERE a.fieldname = b.fieldname AND a.fieldname2 = b.fieldname2 AND ....so on.. to identify the duplicate rows....)
Этот запрос сохранит самую старую запись в базе данных для критериев, выбранных в WHERE CLAUSE .
Oracle Certified Associate (2008)
Я не видел ответов, которые используют общие выражения таблиц и функции окна. Это то, с чем мне легче всего работать.
DELETE FROM YourTable WHERE ROWID IN (WITH Duplicates AS (SELECT ROWID RID, ROW_NUMBER() OVER(PARTITION BY First_Name, Last_Name, Birth_Date) AS RN SUM(1) OVER(PARTITION BY First_Name, Last_Name, Birth_Date ORDER BY ROWID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS CNT FROM YourTable WHERE Load_Date IS NULL) SELECT RID FROM duplicates WHERE RN > 1);
Что следует отметить:
1) Мы проверяем только дублирование полей в разделе.
2) Если у вас есть причина выбрать один дубликат над другими, вы можете использовать предложение order by, чтобы эта строка имела row_number () = 1
3) Вы можете изменить дубликат числа, сохраненный, изменив окончательное предложение where на «Where RN> N» с N> = 1 (я думал, что N = 0 удалит все строки с дубликатами, но просто удалит все строки) ,
4) Добавлено поле раздела Sum, запрос CTE, который будет помечать каждый fow номерами строк в группе. Поэтому для выбора строк с дубликатами, включая первый элемент, используйте «WHERE cnt> 1».
1. решение
Delete from emp where rowid not in (select max(rowid) from emp group by empno);
2. slution
Delete from emp where rowid in (select rid from (select rowid rid, row_number() over(partition by empno order by empno) rn from emp) where rn > 1);
3.solution
Delete from emp e1 where rowid not in (select max(rowid) from emp e2 where e1.empno = e2.empno);
4. решение
Delete from emp where rowid in (select rid from (select rowid rid, dense_rank() over(partition by empno order by rowid) rn from emp) where rn > 1);
Create or replace procedure delete_duplicate_enq as cursor c1 is select * from enquiry; begin for z in c1 loop delete enquiry where enquiry.enquiryno = z.enquiryno and rowid > any (select rowid from enquiry where enquiry.enquiryno = z.enquiryno); end loop; end delete_duplicate_enq;
Create table abcd(id number(10),name varchar2(20)) insert into abcd values(1,"abc") insert into abcd values(2,"pqr") insert into abcd values(3,"xyz") insert into abcd values(1,"abc") insert into abcd values(2,"pqr") insert into abcd values(3,"xyz") select * from abcd id Name 1 abc 2 pqr 3 xyz 1 abc 2 pqr 3 xyz Delete Duplicate record but keep Distinct Record in table DELETE FROM abcd a WHERE ROWID > (SELECT MIN(ROWID) FROM abcd b WHERE b.id=a.id); run the above query 3 rows delete select * from abcd id Name 1 abc 2 pqr 3 xyz
Платформа Oracle позволяет удалять строки из таблиц, представлений, материализованных представлений, вложенных подзапросов и секционированных представлений и таблиц следующим образом.
{имя_таблицы | ONLY {имя_таблицы)} [псевдоним] [{PARTITION {имя_раздела) SUBPARTITION
(имя_подраздела)}] | {подзапрос }]) | TABLE {выражение_для_коллекции) [(+)]}
INTO переменная [, …]]
Параметры приведены ниже.
имя_таблицы [псевдоним]
Указывается таблица, представление, материализованное представление или секционированная таблица или представление, откуда будут удаляться записи. При желании вы можете перед именем_таблицы указать схему или указать после имени таблицы соединение с базой данных. В противном случае система Oracle будет использовать текущую схему и локальный сервер базы данных. При желании вы можете присвоить имени_таблицы какой-нибудь псевдоним. Псевдоним является необходимым, если таблица ссылается на атрибут или метод объектного типа.
PARTITION имя раздела
Операция удаления применяется к указанному разделу, а не ко всей таблице. При удалении из секционированной таблицы необязательно указывать имя раздела, но это во многих случаях помогает уменьшить сложность предложения WHERE.
SUBPARTITION (имя_подраздела)
Удаление применяется к указанному подразделу, а не ко всей таблице.
(подзапрос }])
Указывается, что целью операции удаления является вложенный подзапрос, а не таблица, представление или другой объект базы данных. Параметры данного предложения следующие.
подзапрос
Указывается инструкция SELECT, которая представляет собой подзапрос. Можно создавать любой стандартный подзапрос, но он не может содержать предложения ORDER BY.
WITH READ ONLY
Указывается, что подзапрос не может быть обновлен.
WITH CHECK OPTION
Система Oracle будет отклонять любые изменения удаленной таблицы, которые не видны в результирующем наборе данных подзапроса.
CONSTRAINT имя_ограничения
Система Oracle ограничит вносимые изменения, основываясь на ограничении с именем имя_ограничения.
TABLE (выражение Для роллекции) [(+)]
Система Oracle будет обрабатывать выражеиие_дляроллекции как таблицу, хотя фактически это может быть подзапрос, функция или другой конструктор коллекции. В любом случае значение, возвращаемое выражением Для роллекции, должно представлять собой вложенную таблицу или VARRAY.
RETURNING выражение
Извлекаются строки, затронутые командой там, где команда DELETE обычно возвращает только количество удаленных строк. Предложение RETURNING можно применять, если целью команды является таблица, материализованное представление или представление по одной базовой таблице. Если предложение используется при удалении одиночной строки, то значения из удаленной строки, которые определяются выражением, сохраняются в переменных PL/SQL и переменных связывания (bind variables). Если предложение используется при удалении многих строк, то значения из удаленных строк, которые определяются выралсени&м, сохраняются в массивах связывания (bind arrays).
INTO переменная
Указываются переменные, в которые записываются значения, возвращаемые в результате работы предложения RETURNING.
При выполнении инструкции DELETE Oracle возвращает освободившееся в таблице (или базовой таблице представления) место обратно в таблицу или индекс, где хранились данные.
Если данные удаляются из представления, представление не может содержать операций над множествами, ключевого слова DISTINCT, соединений, агрегатной функции, аналитической функции, подзапросов SELECT, предложения GROUP BY, предложения ORDER BY, предложения CONNECT BY или START WITH.
Ниже приводится пример, в котором мы удаляем данные с удаленного сервера.
DELETE FROM scott sales@chicago;
В следующем примере
мы удаляем данные из производной таблицы, указанной в выражении для коллекции.
DELETE TABLE(SELECT contactname FROM customers
с WHERE c.customerid="BOTTM") s WHERE s. region IS NULL OR s.country="MEXICO";
А вот пример удаления из раздела.
DELETE FROM sales PARTITION (sales_q3_1997) WHERE qty > 10000;
И наконец, в следующем примере мы используем предложение RETURNING, чтобы увидеть удаленные значения.
DELETE FROM employee WHERE job_id=13
AND hire_date + TO_YMINTERVAL("01-06") =.
В предыдущем примере удаляются записи из таблицы employee, а значения j obi vl возвращаются в заранее определенную переменную:intol.