Urgent - Convert DQL to SQL

rpowid

We have a custom type that contains a field company_department.  Due to department restructuring, it is necessary to change the company_department drop-down (done via Composer) and the current company_department values.  I would like to run the update with SQL Oracle since there are millions of changes.

I have logged into DA and ran the select statement and converted the DQL to 'Show in SQL' - same results (great).
When I run the following update statement in DQL...

update custom_type object

set company_department = 'Membership and Contact Centre Services'

where company_department = 'Contact Centre'

the SQL (in attachment) is completely different...

How do I convert the DQL to SQL to have it run in Oracle SQL.

Thank you

Jeremy Saumen

It will update all versions.

To update only the current ones, you should add to the where clause :

and r_object_id in (select r_object_id from dm_sysobject_s where i_has_folder = 1 and r_object_type = 'custom_type')

NB : the part r_object_type = 'custom_type' is unneccessary, it's just here to limit the number of rows of the in clause ; be careful though, if you add this clause, it will only update the objects of the type custom_type and not the extended types (if any).

ahmadtuma

The most easist way is to generate an Update SQL query using DA, like you did with the select query.

Since you dont want to execute your update query for all the millions records, limit your update query with:

update custom_type object

set company_department = 'Membership and Contact Centre Services'

where company_department = 'Contact Centre'  and r_object_id = '<some object id>'

In the generated SQL query you only have to remove the last condition : r_object_id = '<some object id>'

rpowid

When I executed the DQL - 'Update' statement and checked the SQL box, the returned query was a SQL '[Select' statement and not a SQL Update one.

I think that this is the functionality of converting DQL to SQL from DA, the SQL results are the count of updated records and not the actual 'Update' statement.

My quest continues......

Alvaro_de_Andres

every custom object is represented in DB in two tables (and views): object_type_s (single attributes) and object_type_r (repeating attributes). If you don't need to use inherited types you can update those tables directly (ie: update obj_type_s set custom_att='new value' where custom_att='old_value'), otherwise you'll need to join with dm_sysobject_s/r and/or any other supertype.

Just make sure you test changes first as this is a not supported/recommended/etc way to do an update

rpowid

I would only like to update the company_department on the current version of the documents.  Would the code you provided update all versions?

The company_department is not a repeating value and found on the custom_type_s table.

Jeremy Saumen

It will update all versions.

To update only the current ones, you should add to the where clause :

and r_object_id in (select r_object_id from dm_sysobject_s where i_has_folder = 1 and r_object_type = 'custom_type')

NB : the part r_object_type = 'custom_type' is unneccessary, it's just here to limit the number of rows of the in clause ; be careful though, if you add this clause, it will only update the objects of the type custom_type and not the extended types (if any).