I have a table in Oracle of which one of the columns (named CONTENTSTRING) is a CLOB. However, some of the rows in this table have identical data in this column. What I'd like to do is remove all the rows except for one that have this identical data. How can I accomplish this?
Googling around, I see a ton of examples for comparing two columns. I also see examples comparing between two different tables. What I don't see is an example using one table and just comparing the rows! I do think I might need to use this function: dbms_lob.compare. However, I'm still not sure how I can set this function up.
From a programmer's perspective, I would think maybe I should do something like:
SELECT CONTENTSTRING FROM TABLE_ALPHA A
and then somehow do another select from the same table as TABLE_ALPHA B, and then use dmbs_lob.compare to compare the two columns. If the row numbers are different AND the column contents are equal, then the row from TABLE_ALPHA B can be deleted.
I think that's the right approach, but how exactly would I write this out in Oracle using SQL? I would appreciate any help or resources on this. Thanks!
DELETE
FROM TABLE_ALPHA A
WHERE EXISTS (
SELECT 1 FROM TABLE_ALPHA B
WHERE DBMS_LOB.COMPARE(A.CONTENTSTRING, B.CONTENTSTRING) = 0
AND A.ROWID > B.ROWID
)
This deletes all dublicates except first one.