Re: Weird ORACLE update statement

From: <bamon_at_ocvaxc.cc.oberlin.edu>
Date: 9 Dec 92 21:15:30 GMT
Message-ID: <1992Dec9.171530.1_at_ocvaxc.cc.oberlin.edu>

In article <1992Dec8.173506.7648_at_pony.Ingres.COM>, jonm_at_Ingres.COM (Don't Judge a book by your cover) writes:
> In article <1992Dec8.153620.9115_at_infonode.ingr.com>, mcintyre@infonode.ingr.com (John Scott Mcintyre) writes...

>>	I am using ORACLE and want to be able to update rows in table1 using
>>	columns from table2 when the two tables can be joined.
>> 
>>	The SQL statement I would like to perform would look like this:
>> 
>>	update table1 set table1.manufacturer=table2.manufact,
>>		      set table1.pur_cost=table2.sale_cost
>>		where table1.inv_number=table2.inv_number;
>> 
>>	The problem is UPDATE apparently cannot handle multiple tables.
>> 

>
> Try using a correlated update ie UPDATE FROM...
>
> Try...
> UPDATE table 1
> FROM table2
> SET table1.manufacturer=table2.manufact,
> table1.pur_cost=table2.sale_cost
> WHERE table1.inv_number=table2.inv_number;
>
> _____________________________________________________________________________
> |Jon Machtynger | Ahhh Brisbane, center of the world. How I miss a Silvio's
> |jonm_at_ingres.com | Pizza A Jug of Powers and a good spew in the Mall.
> +----------------+
>

Wow! I was really excited about this shortcut, until I tried it. It doesn't work with my version of Oracle (v6.0.36.5.0).

SQL> select * from table1;

FIELD

---

one
two
three
four
five

SQL> select * from table2;

FIELD

---

ONE
TWO
THREE
FOUR
FIVE SQL> update table1
  2 from table2
  3 set table1.field1 = table2.field1
  4 where upper(table1.field1) = table2.field1; from table2
*
ERROR at line 2:
ORA-00971: missing SET keyword

   [Hmmmm. Well, let's try a different order.]

SQL> update table1
  2 set table1.field1 = table2.field1
  3 from table2
  4* where upper(table1.field1) = table2.field1 from table2
*
ERROR at line 3:
ORA-00933: SQL command not properly ended

   [I think this is not valid SQL syntax for Oracle.]

---

Jennifer R. Amon     FREENET: aa1190_at_freenet.lorain.oberlin.edu
Oberlin College      FREENET: cc312_at_cleveland.freenet.edu

Oberlin, OH 44074 *INTERNET: bamon_at_ocvaxc.cc.oberlin.edu PH: (216) 775-6987 BITNET: bamon%ocvaxc.cc.oberlin.edu_at_ocvaxc.bitnet FAX: (216) 775-8573 *use the internet address if possible

---

Received on Wed Dec 09 1992 - 22:15:30 CET