Re: sql error
- From: William Robertson <william@xxxxxxxxxxxxxxxxxxxx>
- To: "oracle-l (oracle-l@xxxxxxxxxxxxx)" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 17 Jun 2014 23:54:13 +0100
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@xxxxxxxxxx> 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@xxxxxxxxxx |
www.micros.com
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx
<oracle-l-bounce@xxxxxxxxxxxxx>] *On Behalf Of *Sweetser, Joe
*Sent:* Monday, 16 June, 2014 15:06
*To:* Brian.Zelli@xxxxxxxxxxxxxxx; oracle-l (oracle-l@xxxxxxxxxxxxx)
*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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx
<oracle-l-bounce@xxxxxxxxxxxxx>] *On Behalf Of *Zelli, Brian
*Sent:* Monday, June 16, 2014 12:54 PM
*To:* oracle-l (oracle-l@xxxxxxxxxxxxx)
*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
Other related posts:
