Re: Question on Update

On 23-Apr-98 18:36:29 jeff.w.mcclure wrote:

>Hi folks,

>I have a table (alertinfo) containing columns:

>alert number,
>...
>(other columns)
>...
>ticket number,
>tstatus varchar2(15)

>I have a second table (ticketinfo) containing columns:

>alert number,
>ticket number,
>status varchar2(15)

>I need to update the alertinfo table setting the ticket and status
>equal to the fields in ticketinfo where the alert columns match.
>I have tried many variations of update without success. The problem
>appears to be in the outer where clause.

>update alertinfo
>set (ticket, tstatus) =
>(select ticket, status
> from ticketinfo)
>where ticketinfo.alert = alertinfo.alert;

>Can someone provide some assistance?

Hello Jeff,
there is a possibility if you have Oracle 7.3 or higher and have a unique index on ticketinfo(alert).
You can do the following:

update

   (select

       a.ticket tticket,
       a.tstatus,
       t.ticket,
       t.status
    from
       alertinfo a,
       ticketinfo t
    where
       a.alert=t.alert)

   tticket=ticket,
   tstatus=status;

This will do the job. The above statment updates an inline view. In Oracle 7.3 they introduced the concept of updatable join view. If you create a view for the above select statement you can verify which columns can be updated by querying USER_UPDATABLE_COLUMNS.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |