Re: Help -- NULL value assignment

From: Stephan Born <stephan.born_at_beusen.de>
Date: Mon, 25 Oct 1999 11:30:25 +0200
Message-ID: <38142331.CC56ED75@beusen.de>

> Hi there,
>
> I'd like to have the statement to produce the result
> like
>
> update p_item a
> set a.unit = NVL((select b.unit
> from histpay b
> where b.employee# = a.employee#
> ),a.unit)
>
> I know this is not working, but I want to keep the
> value of "a.unit" if the "select" gets no value. Anyone
> knows how to do it without writing PL/SQL procedure?
>
> Thanks in advance.
> Frank

I didn't try it but don't you can do this:

update p_item a
set a.unit = (select nvl(b.unit, a.unit) from histpay b where b.employee# = a.employee#)

but this will update EVERY line in your table....

Use this instead:

update p_item a
set a.unit = (select b.unit

                   from histpay b
                   where b.employee# = a.employee#)

where a.employee# in (select b.employee# from histpay b where b.unit is not
null)

Regards, Stephan
--

---

Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-17          | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------

Received on Mon Oct 25 1999 - 04:30:25 CDT