Re: Update Problem

From: Peter Schneider <pschneider.ctj_at_metronet.de>
Date: 1997/09/24
Message-ID: <34285e61.424546@pop-news.metronet.de>#1/1

On Tue, 23 Sep 1997 13:06:18 -0700, Satar Naghshineh <satarnag_at_worldsite.com> wrote:

>hi
>What are we doing wrong?
>
>update oldtransactions
>set custid = transactions.custid
>where oldtransactions.cc = transactions.cc
> *
>ORA-00904: Invalid Column Name
>
>
>cc is a column in a table called transactions, so why is Oracle giving
>us this message?
>
>Thanks in Advance,
>Satar Naghshineh

Hi Satar,

In a SET-claue, you may only use an expression (which may be a column of the table being updated) or a subquery. You cannot use a column from another table.

From your statement, I cannot figure out how your tables relate to each other, and how you want the update on table oldtransactions to be restricted, but given that cc is primary/unique on table transactions and oldtransactions.cc is mandatory foreign key referencing transactions.cc, the following will work:

UPDATE oldtransactions ot

   SET ot.custid =

      (SELECT t.custid   -- Single row subquery, MUST return ONE row
         FROM transactions t
        WHERE t.cc = ot.cc)

 WHERE ... /* place your restriction on table ot here */

Hope this helps,
Peter

Peter Schneider
pschneider.ctj_at_metronet.de Received on Wed Sep 24 1997 - 00:00:00 CDT