O'Reilly logo

Oracle and PL/SQL Recipes: A Problem-Solution Approach by Matt Arena, Josh Juneau

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

8-10. Finding All Tables That Include a Specific Column Value

Problem

You are required to update all instances of a particular data column value across multiple tables within your database.

Solution

Search all user tables for the particular column you are interested in finding. Create a cursor that will be used to loop through all the results and execute a subsequent UPDATE statement in each iteration of the loop. The UPDATE statement will update all matching column values for the table that is current for that iteration of the cursor.

The following example shows how this technique can be performed. The procedure will be used to change a manager ID when a department or job position changes management.

CREATE OR REPLACE PROCEDURE change_manager(current_manager_id ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required