Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 Query to add matching class column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
senthamizh

New User


Joined: 27 Apr 2009
Posts: 22
Location: mumbai

PostPosted: Fri Jan 15, 2016 8:32 pm    Post subject: DB2 Query to add matching class column
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: 1932
Location: UK

PostPosted: Sat Jan 16, 2016 3:55 pm    Post subject:
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: 22
Location: mumbai

PostPosted: Sat Jan 16, 2016 4:37 pm    Post subject: Reply to: DB2 Query to add matching class column
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: 1932
Location: UK

PostPosted: Sat Jan 16, 2016 8:00 pm    Post subject:
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: 411
Location: USA

PostPosted: Sun Jan 17, 2016 4:28 am    Post subject: Reply to: DB2 Query to add matching class column
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

Senior Member


Joined: 21 Sep 2010
Posts: 1827
Location: NY,USA

PostPosted: Fri Jan 29, 2016 2:44 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm
No new posts Matching and unmatched records!! Vignesh Sid SYNCSORT 3 Thu Nov 09, 2017 11:22 am
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 4 Tue Nov 07, 2017 8:34 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us