CODEWITHSUNDEEP
sqloracle-databaseduplicates
Johnathan Au
Johnathan Au

Reputation: 5362

How can I turn this into a DELETE statement in ORACLE?

I have a select statement which returns duplicates in a table. I want to now be able to delete these duplicates. How can I do this with ORACLE SQL?

SELECT
  c1.name                     AS c1name,
  c2.name                     AS c2name,
  c1.cy_code                  AS c1country,
  c2.cy_code                  AS c2country,
  c1.latitude                 AS c1lat,
  c1.longitude                AS c1long,
  c2.latitude                 AS c2lat,
  c2.longitude                AS c2long,
  calc_distance(c1.latitude,
                c1.longitude,
                c2.latitude,
                c2.longitude) AS distance
FROM city_temp c1,
  city_temp c2
WHERE c2.name = c1.name
  AND c2.cy_code = c1.cy_code
  AND calc_distance(c1.latitude, c1.longitude, c2.latitude, c2.longitude) = 0
  AND c1.latitude = c2.latitude
  AND c1.longitude = c2.longitude;

Upvotes: 0

Views: 89

Answers (4)

OraNob
OraNob

Reputation: 694

The classic way to delete duplicate rows is

DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values)

Just plug your SELECT statement into it.

Or for ultimate speed on large data sets - you could copy the distinct data across into a temporary table, truncate the original, copy the data back and drop the temporary table.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You would use an exists clause to delete all rows that have equals with higher rowids:

delete from city_temp c1
where exists
(
  select *
  from city_temp c2
  where c2.name = c1.name 
    and c2.cy_code = c1.cy_code 
    and c2.latitude = c1.latitude 
    and c2.longitude = c1.longitude
    and calc_distance(c1.latitude, c1.longitude, c2.latitude, c2.longitude) = 0 
    and c2.rowid > c1.rowid
);

Upvotes: 1

narendra solanki
narendra solanki

Reputation: 412

you can try this if you could change the function calc_distance

DELETE FROM city_temp
WHERE rowid not in
(SELECT MIN(rowid)
FROM city_temp
WHERE calc_distance(latitude, longitude)=0
GROUP BY name, cy_code, latitude,longitude
)

Upvotes: 1

San
San

Reputation: 4538

Using rowid, you can perform this

DELETE FROM city_temp where rowid in (
            SELECT c1.rowid
            FROM city_temp c1, city_temp c2
            WHERE c2.name = c1.name  and c2.cy_code = c1.cy_code and calc_distance(c1.latitude, c1.longitude, c2.latitude, c2.longitude)=0 
                        and c1.latitude = c2.latitude and c1.longitude = c2.longitude)

Upvotes: 0

Related Questions