DQL update objects limit results of subquery

Hartmut Clausen
Hartmut Clausen
in Documentum #1

Hi,

I am trying to link exactly 1000 documents to a folder with a DQL update statement:

update dm_document link '/folder/path/1000-folder' where r_object_id in (select r_object from dm_document ... )

Using enable(RETURN_TOP 1000) in the subquery does not have any effect. Is there another way to accomplish the limit of 1000 documents?

Thanks,
Hartmut

Comments

  • DCTM_Guru
    DCTM_Guru
    #2

    Even if it did work, this wouldn't help you b/c how would you do the next 1000.

    What we do is use Excel to generate individual update statements (using r_object_id as key) and pipe it into DQL script file. We then use IDQL.exe utility on Content Server to run the script.

  • Hartmut Clausen
    Hartmut Clausen
    #3

    I would like to avoid using external tools like Excel, but probably that will be the last resort.

    For the query itself, that works when you extend the condition with ... and not folder('/folder/path', descend'). With that the second query will never find objects updated by the first query.

  • fmejwagner
    fmejwagner
    #4

    Hi Hartmut, old fellow!

    Quite sure you can not do that subquery limit through DQL + ENABLE(RETURN_TOP n)... documentation also says:
    If the query includes a subquery the hint (RETURN_TOP n) is not applied to the subquery.

    But what about this approach:
    If that update is more than a one time fix but a repeated task you may think about creating+registering an underlying (Oracle?) RDBMS view for your subquery. That dynamic view should have the (Oracle?) query result ROW-NUMBER available as a column to be used as a filter (...and row-number < 1000...) in your DQL update statement's where condition...

Categories