Re: Simple Update Question

Thanks much appreciated. My brain goes into a comose sometimes :)

In article <t82n6sslpecirg6985bg0r8v9si3hdulct_at_4ax.com>,   tkyte_at_us.oracle.com wrote:
> 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
>

--
Ender Wiggin

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 30 1999 - 12:12:16 CST