RE: fetch out of sequence

From: Anthony Molinaro <amolinaro_at_wgen.net>
Date: Mon, 23 Aug 2004 13:39:33 -0400
Message-ID: <D17DB304A9F42B4787B68861F9DAE61C51D2CB@wgdc02.wgenhq.net>

The syntax is fine, it's just that a and b no longer exist outside the parenthesis.

update (
select a.last_name a_last_name, b.last_name b_last_name

       from emp a, person b
        where a.emp_id=3Db.person_id

)
set a_last_name=3Db_last_name;

-----Original Message-----
From: Gogala, Mladen [mailto:Mladen.Gogala_at_aetn.com]=20 Sent: Monday, August 23, 2004 1:35 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: fetch out of sequence

Completely incorrect syntax. You cannot update a column=20
>From one table with a column from another just like that. Rewrite that
as a short PL/SQL script, and everything will Be fine. Nice try, but no cigar.

--
Mladen Gogala
A & E TV Network
Ext. 1216


-----Original Message-----
From: Paula_Stankus_at_doh.state.fl.us
[mailto:Paula_Stankus_at_doh.state.fl.us]=20
Sent: Monday, August 23, 2004 1:31 PM
To: Paula_Stankus_at_doh.state.fl.us; oracle-l_at_freelists.org
Subject: RE: fetch out of sequence


Rewriting into an update - I am testing a multiple column - join update:

  1  update
  2  (select a.last_name, b.last_name
  3     from emp a, person b
  4      where a.emp_id =3D3D b.person_id)
  5* set a.last_name =3D3D b.last_name
SQL> desc person;
 Name                                      Null?    Type
 ----------------------------------------- -------- =3D
----------------------------
 PERSON_ID                                          NUMBER
 LAST_NAME                                          VARCHAR2(50)
 FIRST_NAME                                         VARCHAR2(50)
 LOADDATE                                           DATE

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- =3D
----------------------------
 EMP_ID                                             NUMBER(38)
 LAST_NAME                                          VARCHAR2(50)
 FIRST_NAME                                         VARCHAR2(50)
 LOADDATE                                           DATE


When I execute the above update, I get the error:

set a.last_name =3D3D b.last_name
                  *
ERROR at line 5:
ORA-00904: "B"."LAST_NAME": invalid identifier

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Aug 23 2004 - 12:35:49 CDT