Pro/I Help - SQL tips

SQL Hack : Change Attribute values

WARNING: Use these techniques and sample scripts at your own risk.
Always make sure you have a current and reliable BACKUP of the Intralink Commonspace before making any changes at SQL level.

Sometimes you may need to change many values without up-revving any objects...
For example - a bulk update of descriptions or materials.
Working with SQL allows you to change values without the work involved in checking objects out and back in again.
Note - these commands should be used WITH EXTREME CARE. Make a backup of the database before continuing.

The following query will extract relevant codes for the attribute "attribute_name".
Either enter the 4 lines directly into SQL, or save them as a file and call from SQL (eg: @attr_query.sql)

attr_query.sql
select DBAATTRTABLE, DBAATTRCOL from pdm.pdm_dbattrdef d, pdm.pdm_classattr c where d.claid = c.claid and c.claname = 'attribte_name';

The output from this query will be like this:

    DBAATTRTABLE
    -------------------------
    DBAATTRCOL
    -------------------------
    PDM_PRODUCTITEM_VUDA1
    UDA489

The last 2 lines are important - use these values in the next step.

"SET DEFINE OFF" turns off substitution checking, allowing you to use the ampersand "&" in values.
The next 3 lines update the specified table column with "NEW VALUE" substituted for each occurence of "OLD VALUE".
The last line commits the change (as long as no errors have occurred).
As before, you may either enter the code directly or save and call from SQL (eg: @attr_change.sql)
You could also add many changes into one file - just repeat the middle 3 lines for each different value.

attr_change.sql
SET DEFINE OFF update pdm.pdm_productitem_vuda1 set uda489='NEW VALUE' where uda489='OLD VALUE'; commit;