Update all tables that have a specific data in a specific (or similar) column(s)

A while ago, I needed to rename a user (OPRID) in PeopleSoft because it was created without the correct naming conventions and it could overlap with a future user that would conform to the standards.

No problem, I deleted the user and created a new one, but how about some of the processes and that he ran and some of the things that he changed?

I created a select to find all tables that have the OPRID column and other columns with similar names and lengths (sometimes it’s called LAST_OPRID, etc… and I’ve seen columns that store the OPRID with different data lengths!). In the interest of the future, I didn’t create a SELECT only for OPRID, I made it easy to adapt to other columns too…

Here it is, instructions on how to use it below:

WITH DATA_TO_REPLACE AS

     (SELECT 'SYSADM'    AS TABLE_OWNER          --CHANGE THIS

            ,'OPRID'     AS COLUMN_LIKE          --CHANGE THIS

            , 5          AS MINIMUM_SIZE         --CHANGE THIS

            , 55         AS MAXIMUM_SIZE         --CHANGE THIS

            , 'VARCHAR2' AS COL_TYPE_LIKE        --CHANGE THIS

            , 'NEWVALUE' AS NEW_VALUE            --CHANGE THIS

            , 'OLDVALUE' AS OLD_VALUE_TO_REPLACE --CHANGE THIS

            , UPPER('UPDATE SUB_TABLE_NAME SET SUB_COLUMN_NAME = ''''SUB_NEW_VALUE'''' WHERE SUB_COLUMN_NAME = ''''SUB_OLD_VALUE_TO_REPLACE'''';') AS SQL_TO_RUN

      FROM DUAL)

SELECT 'SELECT ''' || REPLACE(REPLACE(REPLACE(REPLACE(SQL_TO_RUN,'SUB_TABLE_NAME',TABLE_NAME)

                                                                ,'SUB_COLUMN_NAME',COLUMN_NAME)

                                                                ,'SUB_NEW_VALUE',NEW_VALUE)

                                                                ,'SUB_OLD_VALUE_TO_REPLACE',OLD_VALUE_TO_REPLACE)

                    || ''' FROM ' || TABLE_NAME || ' WHERE ' || COLUMN_NAME || '= ''' || OLD_VALUE_TO_REPLACE || ''' AND ROWNUM = 1 UNION'

--SELECT*

FROM ALL_TAB_COLUMNS, DATA_TO_REPLACE

WHERE OWNER= TABLE_OWNER

AND COLUMN_NAME LIKE '%' || COLUMN_LIKE || '%'

AND DATA_LENGTH BETWEEN MINIMUM_SIZE AND MAXIMUM_SIZE

AND DATA_TYPE LIKE '%' || COL_TYPE_LIKE || '%'

---- CHANGE ANYTHING BELOW THIS LINE

--ONLY TABLES "PS_"

AND TABLE_NAME LIKE 'PS\_%' ESCAPE '\'

-- NO AET, TMP, TAO, TEO...

AND TABLE_NAME NOT LIKE '%AET'

AND TABLE_NAME NOT LIKE '%TMP' AND TABLE_NAME NOT LIKE '%TMP_' AND TABLE_NAME NOT LIKE '%TMP__'

AND TABLE_NAME NOT LIKE '%TAO' AND TABLE_NAME NOT LIKE '%TAO_' AND TABLE_NAME NOT LIKE '%TAO__'

AND TABLE_NAME NOT LIKE '%TEO' AND TABLE_NAME NOT LIKE '%TEO_' AND TABLE_NAME NOT LIKE '%TEO__'

-- NO VIEWS

AND TABLE_NAME NOT IN (SELECT VIEW_NAME FROM ALL_VIEWS WHERE OWNER = TABLE_OWNER)

You need to review and change the first few values (“CHANGE THIS”) and the tables criteria below the “CHANGE ANYTHING BELOW THIS LINE”.

Then run this, don’t worry, it’s just a SELECT statement, it will bring up the list of tables that *may* contain data that you might want to replace.

Copy the resulting SELECT … UNION … SELECT …, paste, remove the last UNION and run it (again, safe so far…)

This will result in the UPDATE statement for all tables that contain data equals to the data you wanted to replace only (say the table A has the column USER that you want to replace but this column doesn’t have the data “MYUSER” that you wanted to replace with “OTHERUSER”, then it won’t show up).

If you have any doubts, post a comment and I’ll try to help…