Re: Update All Tables Containing Column

On Sun, 07 Apr 2002 11:35:51 GMT, "Linda Turner" <LindaTurner_at_cdgx.com> wrote:

>Thanks Ivan. I realize that I can do this. I am hoping to do it
>programatically, using dynamic SQL, and for each table containing the
>column, update that column.
>
>Thanks again.
>
>

declare
sqlstr varchar2(1000);
newid number; -- or varchar2 whatever
begin
newid := '<any constant>'
for r in (select table_name from user_tab_columns where column_name = 'USERID') loop
sqlstr := 'update '||r.table_name||' set userid = :newid'; execute immediate sqlstr using newid;
end loop;
commit;
end;
/
Is it *that* easy? Yes, it is.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

>"Alex Ivascu" <alexdivascu_at_yahoo.com> wrote in message
>news:96Or8.119565$QB1.28168414_at_typhoon3.we.ipsvc.net...
>> You can run a simple dynamic sql like this one, to accomplish what
>you want:
>>
>> SELECT 'UPDATE '||TABLE_NAME||' SET USERID=''TEST'';'
>> FROM USER_TAB_COLUMNS
>> WHERE COLUMN_NAME = 'USERID';
>>
>> Hope this helps.
>>
>> Alex Ivascu
>> DBA
>>
>>
>> "Linda Turner" <LindaTurner_at_cdgx.com> wrote in message
>> news:FpBr8.18239$XP2.7296902_at_typhoon.southeast.rr.com...
>> > I am new to Oracle. We don't use Pl/SQL at our site, since we use
>> > 3-rd party software. I need to look through the Oracle dictionary
>> > (user_tab_columns) for all tables containing a particular column
>> > ("USERID"), and update that to a different value.
>> >
>> > Can someone provide me with a brief example of how to do this in
>> > PL/SQL?
>> >
>> >
>>
>>
>>
>>
>
Received on Sun Apr 07 2002 - 08:49:22 CDT