Re: update query question

On 22 Oct 2004 13:59:24 -0700, j.cawkwell_at_open.ac.uk (Jack) wrote:

>I am trying to update the notification.notify_key
>if it is null, otherwise leave it unchanged. It
>should be set to the appropriate value from
>notify_temp.notify_key.
>
>The following code sets it correctly on the first
>execution, then on the second execution it is set
>to null. This repeats cycling between a value and
>null each time. (not what is needed)
>
>update notification nf set notify_key=(
> select
> decode(nf.notify_key,NULL, tm.notify_key,
> nf.notify_key,nf.notify_key)
> from
> notify_temp tm
> where
> nf.d_machine_key = tm.d_machine_key (+) )
>
>Any suggestions?

Generally speaking if you have a column updated by a subquery, you need to have that subquery in the where clause of the update statement. You don't have such a where clause, so the behavior you see is expected, as you update too many rows.

>
>Thanks, Jack

--
Sybrand Bakker, Senior Oracle DBA