EMC Documentum DQL - How to delete repeating attribute

Question

I have a few objects created on my database and I need to delete some of the repeating attributes related to them. The query I'm trying to run is:

UPDATE gemp1_product objects REMOVE ingredients[1] WHERE (r_object_id = '08015abd8002cd68')

But all I get is the folloing error message:

Error querying databse. [DM_QUERY_E_UPDATE_INDEX]error: "UPDATE: Unable to REMOVE tghe attribute ingredients at index 1." [DM_OBJECT_W_DELETE_ATTR_POSITION_ERROR]warning: "attempt to delete non-existent attribute 88"

Object 08015abd8002cd68 exists and I can see it on the database. Queries like SELECT and DELETE work fine but I do not want to delete the whole object.

Answer 1

There is no easy way to do this. The reason is that repeating attributes are ordered, to enable multiple repeating attributes to be synchronized for a given object.

Either

1. set the attribute value to be empty for the given position, and change your code to discard empty attributes, or
2. use multiple DQL statements to shuffle the order so that the last one becomes empty, or
3. change your data model, e.g. use a single attribute as a property bag with pre-defined delimiters.

Details (1)

UPDATE gemp1_product OBJECTS SET ingredients[1] = '' WHERE ...

Details (2)

For each index; first find the value of index+1:

SELECT ingredients
FROM gemp1_product
WHERE (i_position*-1)-1 = <index+1>
ENABLE (ROW_BASED)

Use the value in a new query:

UPDATE gemp1_product OBJECTS SET ingredients[1] = '<value_from_above>' WHERE ...

It should also be possible to do this by nesting DQL somehow, but it might not be worth the effort.