DQL for duplicate object name under same folder

Hi Experts,

My exact requirement is to delete objects which were created in 2009, these objects share same names with objects uploaded in 2010 and both exist in under one folder.

Example:

Folder path is like /Test/ABC under ABC folder I've documents like

MyDocument.txt which was created in 2009 &

MyDocument.txt which was created in 2010.

Now I need to find a query which will list 2009 objects having same named with 2010 objects under same folder, once I get this list by select query, I can then delete them easily.

Could any one know what willl be the DQL.

Please let me know, Thanks in Advanced

Prashant

Find more posts tagged with

Comments

acarver

this may not be optimal SQL syntax for large amounts of data

(I doubt that it is the completely correct BETWEEN syntax, too),

but it should get you started.

delete dm_document object where

folder('/Test/ABC') and

r_creation_date between date('01/01/2009') and date('12/31/2009') and

object_name in

( select object_name from dm_document object where

folder('/Test/ABC') and

r_creation_date between date('01/01/2010') and date('12/31/2010')

)

;

Andrew

apriam_i

Hi!

Try this one:

select doc1.r_object_id, doc1.object_name, doc1.r_creation_date from dm_document doc1, dm_document doc2
where doc1.object_name=doc2.object_name and doc1.r_object_id <> doc2.r_object_id
and folder('/Test/ABC', descend)

KSMatson

I have modified the query from Alexey to fit your requirement. Make sure that the folder path you specify is correct.

Once you have the expected results from the below select query, you can construct the delete query to delete those documents.

select distinct doc1.r_object_id, doc1.object_name, doc1.r_creation_date 
from dm_document doc1, dm_document doc2
where doc1.object_name = doc2.object_name 
and doc1.r_object_id <> doc2.r_object_id
and  folder('/Test/ABC')
and object_name not in (select object_name from dm_document 
where folder ('/Test/ABC') 
and datetostring(r_creation_date, 'yyyy') = '2010');