Re: Question on Update

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: Thu, 23 Apr 1998 21:48:28 GMT
Message-ID: <353fb6d1.11645013@www.sigov.si>

On Thu, 23 Apr 1998 11:36:29 -0600, jeff.w.mcclure_at_ameritech.com wrote:

>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;
>
>I receive an ORA-00904: invalid column name
>referring to ticketinfo.alert. This seems as though it should be simple
>but I am having a very difficult time making it work.
>
>Can someone provide some assistance?

Try this one:

UPDATE alertinfo SET (ticket, status) =   (SELECT ticketinfo.ticket, ticketinfo.status    FROM ticketinfo
   WHERE ticketinfo.alert = alertinfo.alert) WHERE EXISTS (SELECT 1 FROM ticketinfo

                 WHERE ticketinfo.alert = alertinfo.alert);

The WHERE EXISTS condition prevents seting alertinfo.ticket and alertinfo.status to NULL where there is no matching records in the ticketinfo table.

>TIA,
>
>Jeff McClure
>Specialist DB Analyst
>jeff.w.mcclure_at_ameritech.com

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Apr 23 1998 - 16:48:28 CDT