Re: Simple Update Question

Thomas Kyte wrote:

> ...
>
> update tmp_combined_rpt_defn tcrd
> set hier_nbr = (
> select distinct cy.class_id
> from class_yr cy
> where cy.class_nbr = tcrd.class_nbr )
> where exists (
> select distinct cy.class_id
> from class_yr cy
> where cy.class_nbr = tcrd.class_nbr )
>

I think that you will have a problem if the sub-select in the "set hier_nbr = ..." clause returns more than 1 row. You can avoid that by modifying the SQL to the following :

update tmp_combined_rpt_defn tcrd

   set hier_nbr = (

       select distinct cy.class_id
         from class_yr cy
        where cy.class_nbr = tcrd.class_nbr )
 where 1 = (
       select count(*)
         from class_yr cy
        where cy.class_nbr = tcrd.class_nbr );

Rudy Received on Tue Jan 11 2000 - 10:38:04 CST