Home » SQL & PL/SQL » SQL & PL/SQL » Help? Arithmetic operation of columns of different table (SQL Plus 8.0)
Help? Arithmetic operation of columns of different table [message #331716] Fri, 04 July 2008 22:54 Go to next message
niza
Messages: 1
Registered: July 2008
Location: Pakistan
Junior Member
No Message Body
Re: Help? Arithmetic operation of columns of different table [message #331718 is a reply to message #331716] Fri, 04 July 2008 23:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow Posting Guidelines below
http://www.orafaq.com/forum/t/88153/0/

Please RTFM
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#i2112182
& learn to compose valid syntax.



[Updated on: Fri, 04 July 2008 23:35] by Moderator

Report message to a moderator

Re: Help? Arithmetic operation of columns of different table [message #331721 is a reply to message #331716] Sat, 05 July 2008 00:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many of us don't want or can't download files.
Post your question inline and not in attached files.

If you have code or queries, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Help? Arithmetic operation of columns of different table [message #331947 is a reply to message #331716] Sun, 06 July 2008 21:17 Go to previous message
prtz
Messages: 11
Registered: January 2008
Junior Member
Hi Niza,

The syntax of your UPDATE statements is incorrect, hence you are getting those errors.

I am demonstrating a couple of ways to update the table tab1 the way you want.

SQL> --
SQL> drop table tab1;

Table dropped.

SQL> drop table tab2;

Table dropped.

SQL>
SQL> create table tab1 as
  2  select 1 as id, 'ABC' as name, 1500 as sal from dual union all
  3  select 2, 'XYZ', 2000 from dual union all
  4  select 3, 'JKL', 2500 from dual;

Table created.

SQL>
SQL> create table tab2 as
  2  select 1 as id, 500 as inc from dual;

Table created.

SQL>
SQL> --
SQL> select * from tab1;

        ID NAM        SAL
---------- --- ----------
         1 ABC       1500
         2 XYZ       2000
         3 JKL       2500

SQL> select * from tab2;

        ID        INC
---------- ----------
         1        500

SQL>
SQL> --
SQL> -- Update t1.sal to t1.sal + t2.inc for matching IDs in both tables
SQL> --
SQL>
SQL> -- Update statement 1
SQL> update tab1 t1
  2  set t1.sal = t1.sal + (select t2.inc
  3                           from tab2 t2
  4                          where t2.id = t1.id)
  5  where exists (select null
  6                  from tab2 t2
  7                 where t2.id = t1.id);

1 row updated.

SQL>
SQL> --
SQL> select * from tab1;

        ID NAM        SAL
---------- --- ----------
         1 ABC       2000
         2 XYZ       2000
         3 JKL       2500

SQL> select * from tab2;

        ID        INC
---------- ----------
         1        500

SQL>
SQL> -- Another form of UPDATE statement will require that
SQL> -- t1.sal is mapped to a "key-preserved" table.
SQL> -- For that, you will have to ensure that tab2.id is
SQL> -- the primary (or unique) key in table tab2.
SQL> --
SQL> -- Either of the following statements could be run for the
SQL> -- 2nd form of UPDATE statement to work and I'll use
SQL> -- the 1st statement for my demonstration:
SQL> --
SQL> -- (1) Make tab2.id the primary key: alter table tab2 add primary key (id);
SQL> -- OR
SQL> -- (2) Create a unique index on tab2.id : create unique index tab2_u1 on tab2(id);
SQL> --
SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> alter table tab2 add primary key (id);

Table altered.

SQL>
SQL> -- Update statement 2
SQL> update (select t1.sal as t1_sal, t2.inc as t2_inc
  2          from tab1 t1, tab2 t2
  3          where t1.id = t2.id)
  4  set t1_sal = t1_sal + t2_inc;

1 row updated.

SQL>
SQL> --
SQL> select * from tab1;

        ID NAM        SAL
---------- --- ----------
         1 ABC       2000
         2 XYZ       2000
         3 JKL       2500

SQL> select * from tab2;

        ID        INC
---------- ----------
         1        500

SQL>
SQL>
SQL>                                                                      


You may want to refer to the Oracle documentation for the following topics:

(i) Syntax of UPDATE statement
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#SQLRF01708

(ii) Key preserved tables
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/views.htm#sthref3058

HTH
prtz

Previous Topic: how to evaluate sting in if statment
Next Topic: how to call an exe from oracle trigger
Goto Forum:
  


Current Time: Sat Dec 10 22:35:09 CST 2016

Total time taken to generate the page: 0.11195 seconds