IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 Query to add matching class column


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
senthamizh

New User


Joined: 27 Apr 2009
Posts: 23
Location: mumbai

PostPosted: Fri Jan 15, 2016 8:32 pm
Reply with quote

Hi,

I have the below Table
Code:

TABLE1,
REG     CODE       Class         Amount
1      abc            A          10
1      abc            B          2
1      abc            D          2
2      abc            A          2
1         XYZ                L             10


The requirement is to add matching class column Amount and update for REG 2 for Code = abc.

after update the table TABLE1 should look as below,

TABLE1
Code:
REG   CODE   Class      Amount
1   abc               A      10
1   abc              B       2
1   abc              D      2
2   abc              A     12
1      XYZ              L      10


Can some one help me on this?

Thanks,
Sen

Code'd, for what it was worth
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Sat Jan 16, 2016 3:55 pm
Reply with quote

Use SQL to get the data from your table, use your program to play with your data, use SQL to update your table.

If you have large amounts of data and values to change you may have to unload the table and use the unloaded data then reload it.
Back to top
View user's profile Send private message
senthamizh

New User


Joined: 27 Apr 2009
Posts: 23
Location: mumbai

PostPosted: Sat Jan 16, 2016 4:37 pm
Reply with quote

Is this possible in Spufi? with out using program.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Sat Jan 16, 2016 8:00 pm
Reply with quote

Is this a production table? How many rows have to be changed?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Sun Jan 17, 2016 4:28 am
Reply with quote

Will it always be the REG '2' that needs to be updated i.e. the second occurrence of code and class combination? I assume there will Not be any REG 3, 4, 5 etc.

Will you have more code and class combination that needs update under a single REG Or multiple combinations need to be updated?

Please provide bigger sample of data.

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2588
Location: NYC,USA

PostPosted: Fri Jan 29, 2016 2:44 am
Reply with quote

Code:
    update table  c

      set c.Amount =
       (select tbl1.aa 
       from table  b,
       (select b.Class   bb , sum(b.Amount )   aa 
           from table   b  group by  b.Class   ) as tbl1     
       where b.REG= 2  and
       tbl1.bb = b.Class )
       
       where c.REG= 2
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Column names in SYSIBM tables DB2 5
No new posts Matching and non matching records usi... DFSORT/ICETOOL 11
No new posts Need Help with on of the coalesce query DB2 4
No new posts Can we Insert duplicates in Primary U... DB2 2
No new posts Need assistance formatting when joini... SYNCSORT 8
Search our Forums:

Back to Top