Suppose I have a table with column A like following :
a
--
x
y
m
x
n
y
I want to delete all rows that have duplicate a column value and keep just one value. After this operation, my column would be like If you do :
select distinct a from A;
I know how to select rows with repeated a column values But I can't just replace select with DELETE because it would delete the unique values too.
Any help would be greatly appreciated.
In Oracle, you can do this by using the hidden column rowid and a correlated subquery:
delete from a
where rowid > (select min(rowid)
from a a2
where a.a = a2.a
);
Alternatively, you can phrase this as a not in:
delete from a
where rowid not in (select min(rowid)
from a a2
group by a2.a
);
delete statement, so there is no way to do that.ROWID.You can use combination of CTE and Ranking function
;With cte As
(
Select ROW_NUMBER() OVER (PARTITION BY colA ORDER BY colA) as rNum
From yourTable
)
Delete From cte
Where rNum<>1
In SQL, You can use CTE and delete the duplicated rows. See the query below.
WITH CTE AS(
SELECT a,
RN = ROW_NUMBER()OVER(PARTITION BY a ORDER BY a)
FROM A
)
DELETE FROM CTE WHERE RN > 1
delete in Oracle. Can you provide some documentation on this? It returns an error in this simple SQL Fiddle: sqlfiddle.com/#!4/b9bb1/1.