2

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?

Miki
  • 2,493
  • 2
  • 27
  • 39
MrPapaye
  • 23
  • 1
  • 3

1 Answers1

2

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', ...)
cgrim
  • 4,890
  • 1
  • 23
  • 42
  • Thanks for your answer. Indeed, I will have to do it with java using DFC api. – MrPapaye Feb 19 '19 at 07:33
  • You are welcome. It is sad that Documentum is not able to do that - probably all databases does not support that and then it is not possible to translate such DQL in SQL. But you have probably another option if you need to do that as an administrator from DA or any other tool which is able to execute the DQL -> select the 1. `r_object_id` and the 100. `r_object_id` in 2 separate SELECTs ordered by `r_object_id` and then use those IDs in DELETE statement in the WHERE clause like this `r_object_id >= 'id1' AND r_object_id <= 'id100'` – cgrim Feb 19 '19 at 11:19