Re: Question on Update

Your "where" clause should be inside the parentheses of the subquery and not in the outer query. Otherwise, Oracle is looking for it as part of the outer query and only looking at the alertinfo table for valid columns.

--
Matt Brennan
SQL*Tools Specialist
GERS Retail Systems
9725-C Scranton Road
San Diego, California 92121
1-800-854-2263
mbrennan_at_gers.com

jeff.w.mcclure_at_ameritech.com wrote in article <6hnqmd$egl$1_at_nnrp1.dejanews.com>...
> 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;
>
> 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?
>
> TIA,
>
> Jeff McClure
> Specialist DB Analyst
> jeff.w.mcclure_at_ameritech.com
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
>
Received on Fri Apr 24 1998 - 17:11:17 CDT