Home » SQL & PL/SQL » SQL & PL/SQL » Update statement using case with two tables (Oracle 9.2, RHEL4)
Update statement using case with two tables [message #292064] Mon, 07 January 2008 11:13 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I'm attempting an update using a case statement. I have tables
pass2:
SQL> desc pass2
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 ID                                                                         NUMBER
 COUNTRY                                                                    VARCHAR2(2)
 PASSES                                                                     NUMBER
 REV                                                                        NUMBER


SQL> select * from pass2;

        ID CO     PASSES        REV
---------- -- ---------- ----------
     18685 CA         32      12.57
     18685 GB          4        .31
     18685 US         15      49.78
     32375 CA         23       30.6
     32375 GB         26      36.32
     32375 US        438    1833.25
     33414 CA         24       6.39
     33414 US       2818    8096.89
     33415 CA         40      41.53
     33415 US      16550    10633.6
     33415 GB          3        .18


and pass3:
SQL> desc pass3
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 ID                                                                         NUMBER
 PASSES                                                                     NUMBER

SQL> select * from pass3;

        ID     PASSES
---------- ----------
     18685          4
     32375         57
     33414         24
     33415         89

I want to add pass2.passes and pass3.passes which updates pass2.passes with the total that have a country of 'US' and where pass2.id=pass3.id. The result should look like this:
SQL> select a.id, (a.passes+b.passes) passes from 
int_pass2 a, int_pass3 b where
a.id = b.id and
a.country = 'US';  

        ID     PASSES
---------- ----------
     18685         19
     32375        495
     33414       2842
     33415      16639



The below statement obviously fails because pass3 is not defined correctly. What I have been unable to determine is how I can include pass3 into my update statement.
update pass2
set passes = 
case
when id = (select id from pass3)
then passes + pass3.passes
end
where country = 'US';

Thanks.

[Updated on: Mon, 07 January 2008 11:15]

Report message to a moderator

Re: Update statement using case with two tables [message #292068 is a reply to message #292064] Mon, 07 January 2008 11:37 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here are 3 ways:
- Update (select) set ...
- update table set x=(select x+y from ... where join clause) where exist clause
- merge statement

All these are already been explained here.

Regards
Michel
Previous Topic: Any suggestions
Next Topic: dynamic flat file name (merged)
Goto Forum:
  


Current Time: Sun Dec 04 04:33:54 CST 2016

Total time taken to generate the page: 0.16152 seconds