Re: sql error

From: William Robertson <william_at_williamrobertson.net>
Date: Tue, 17 Jun 2014 23:54:13 +0100
Message-ID: <7772647580320389925_at_unknownmsgid>



Indeed - the point being that Oracle's UPDATE syntax is not a join, so there is no U_C_B table to refer to. You would either write it as an inline view as Stephan suggests (u_c_b.u_caid will need to be unique so that the view is key-preserved), or use an equivalent MERGE statement, or else write the lookup from u_c_b as a correlated subquery (bearing in mind you may need to repeat it in the WHERE clause as an IN/EXISTS if you only want to update rows that join).

William Robertson

On 16 Jun 2014, at 20:18, "Uzzell, Stephan" <SUzzell_at_micros.com> wrote:

  Assuming roswellrlm is the schema, and u_callannotate and u_c_b are tables, this seems like the perfect place for an update a join:

Something like this (not tested as I don’t have your schema) might do what you want:

update

        (

          select

                  uc.er

                , ucb.er new_er

                , uc.pr

                , ucb.pr new_pr

                , uc.her2

                , ucb.her2 new_her2

          from

                  U_COLLANNOTATE uc

                , u_c_b ucb

          where

                  uc.collannotateid = ucb.u_caid

        )

set

        er = new_er

      , pr = new_pr

      , her2 = new_her2

/

*Stephan Uzzell* | Senior Database Administrator | Managed Services |MICROS
Systems, Inc.
Direct: 443.285.8000x2345 | Mobile: 443.864.1725 | suzzell_at_micros.com | www.micros.com

*From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org
<oracle-l-bounce_at_freelists.org>] *On Behalf Of *Sweetser, Joe
*Sent:* Monday, 16 June, 2014 15:06
*To:* Brian.Zelli_at_RoswellPark.org; oracle-l (oracle-l_at_freelists.org)
*Subject:* RE: sql error

Could it be the period in the table_name?

roswellrlm.u_c_b.u_caid

roswellrlm.u_c_b*_*u_caid ?

Is rosellrlm a schema?

-joe

*From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org
<oracle-l-bounce_at_freelists.org>] *On Behalf Of *Zelli, Brian
*Sent:* Monday, June 16, 2014 12:54 PM
*To:* oracle-l (oracle-l_at_freelists.org)
*Subject:* sql error

update roswellrlm.U_COLLANNOTATE

set U_COLLANNOTATE.ER = roswellrlm.u_c_b.ER,

    U_COLLANNOTATE.PR = roswellrlm.u_c_b.PR,

    U_COLLANNOTATE.HER2 = roswellrlm.u_c_b.HER2

where u_collannotate.u_collannotateid = roswellrlm.u_c_b.u_caid;

I try and run this and I get:

ORA-00904: "ROSWELLRLM"."U_CE_B"."U_CAID": invalid identifier

What the heck is wrong? At first I thought the name was too long so I shortened it but it is still the same error.

Brian

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jun 18 2014 - 00:54:13 CEST

Original text of this message