[SOLVED] Deleting top 100 objects in DQL statement

Deleting top 100 objects in DQL statement

I need to delete the first top 100 objects from a dm_document table.

I already tried:

DELETE dm_document objects where object_name='TestObject' enable (RETURN_TOP 100);
DELETE dm_document objects where r_object_id in (select r_object_id from dm_document where object_name='TestObject' enable (RETURN_TOP 100));

But both did not work. Do you have any tips/ideas?

Solution

Sadly it is not possible by one DQL query because hints are applied only for SELECT statements when used as a main statement and nor will the sub-select help.

You can solve it using DFC (in Java, Groovy or any language which can work with dfc.jar). Select 100 IDs, join them into one String and provide them into the DELETE statement as an IN clause where all ids will be listed explicitly:

DELETE dm_document OBJECT WHERE r_object_id IN ('0902e70480002d01', '0902e70480002d02', '0902e70480002d03', ...)