Re: Simple Update Question

A copy of this was sent to EnderW <ender29_at_my-deja.com> (if that email address didn't require changing) On Thu, 30 Dec 1999 15:26:30 GMT, you wrote:

>Hi,
> I know my sql stinks and I don't want to write a pl/sql block for a
>simple update. I need two update statements. I will run them one after
>another. The problem is I don't want the update statements to put a
>null value in the column if there is no corresponding value. Here's the
>update
>
>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 )

one way:

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 )

another way:

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 class_nbr in (
       select class_nbr
         from class_yr cy )

>The problem is there are occasions when there is no matching class_id
>for some rows. In that case, I donot want the hier_nbr to be updated. I
>tried something like
>
>select distinct nvl(cy.class_id, hier_nbr) and it didnot work.
>Any presents for the christmas.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Dec 30 1999 - 10:34:17 CST