Re: How to do a subquery

I can't imagine anything easier than native dynamic SQL.

It can be done in a loop with only about a dozen lines of code.

You want easier than that?

Daniel Morgan

Steve wrote:

> I have a need to write a query that updates every table in a schema
> that contains the column name "domain" (a VARCHAR2).
>
> From the data dictionary I know that about 35 tables (out of 70) in my
> schema have the column name "domain". So I want to update all rows
> where the "domain" field equals some value.
>
> I know I can do this using the dynamic sql package, but is there an
> easier subquery that I can do? I tried
>
> SQL> update (select table_name from user_tab_columns where
> column_name='DOMAIN') set domain='new_value' where domain='old_value'
>
> but that doesn't compile. I think the subquery is illegally formed.
> I would like to avoid using the dynamic sql package if I can. Any
> takers?
Received on Sat Mar 23 2002 - 14:59:45 CST