Update multiple records with a single query (Oracle)

Warning: Be very careful using this method to directly update your Sparx EA repository. Most objects in the Sparx EA repository requires updates to multiple tables in the underlying database, unless you know what you’re doing I strongly suggest using the API to do additions and updates in your repository.

I sometimes need to make changes to multiple records in the Sparx EA repository, e.g. a search and replace of a phrase. There are several ways of doing this:

  • Export the information for the elements you want to update to a CSV file, then search and replace in the CSV file, then re-import.
  • Export the information to an XMI file, then search and replace in the XMI file, then re-import
  • Use the API to load, modify and then update() the Package, Element, Diagram etc.
  • Use Repository.Execute() to do the update.

For this post I’m going to focus on how to update multiple objects using a single SQL update statement. This option has the most flexibility, and will allow you to update objects across your repository.

1.0 Updating a “simple” properties in multiple records

Simple properties for objects are only stored in one field in one table, and therefore can be updated with minimal impacts on your repository:

  • The Name, alias, notes for Elements, Diagrams, Attributes, Methods.
  • The Name and alias of Packages, packages store their names in two tables, t_packages and t_objects (same ea_guid in both), both will need to be updated.

I’ve found two way’s to do multiple updates, the CASE method, and the Correlated Method.

For both, I programmatically generate the SQL statement using Sparx API, and then pass the resulting SQL to the Repository.Execute function. I typically make heavy use of my EARecordset library to get the IDs and properties of the objects I want to update.

Specifically I can use my getHierachy() function to get the IDs of the objects I want to update.

1.1 CASE Method

UPDATE t_object
SET t_object.alias = CASE t_object.object_id
WHEN 2329 THEN 'new alias for the element with ID 2329'
WHEN 2330 THEN 'new alias for the element with ID 2330'
ELSE t_object.alias
END
WHERE t_object.object_id IN (2329,2330

1.2 Correlated Method

The Correlated Method uses a similar approach to how I do multiple inserts, I use DUAL and UNION ALL to create a “table” that the values are coming from.

UPDATE t_object
SET (alias, note) =
(
SELECT alias, note FROM
(
SELECT 2329 AS object_id,
'new alias 1' AS alias, 'new note 1' AS note
FROM DUAL
UNION ALL
SELECT 2330 AS object_id,
'new alias 2' AS alias, 'new note 2' AS note
FROM DUAL
) SRC
WHERE t_object.object_id = SRC.object_id
)
WHERE t_object.object_id IN (2329,2330)

2.0 Search and replace

You can use this approach to do a bulk search and replace in your repository, just be careful to limit the change to specific objects or you’ll update your entire repository:

UPDATE t_object t1
SET t1.name =
(
SELECT REPLACE(t_object.Name,'old text','new text'))
WHERE t_object.object_ID = t1.object_ID
)
WHERE t1.object_ID IN (2329,2330)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.