Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum 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: 23
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: 2365
Location: Hampshire, 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: 23
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: 2365
Location: Hampshire, 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: 447
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: 2419
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 Mainframe ISPREDIT Macro query upendrasri TSO/ISPF 3 Wed May 27, 2020 1:12 pm
No new posts Need SQL for two latest records havin... Q5P418 DB2 10 Sun May 10, 2020 8:24 am
No new posts A job with SYSSTC service class is ge... mighty All Other Mainframe Topics 4 Fri Apr 10, 2020 2:31 pm
No new posts Issue while fetching a column(Nullabl... VINAY PALLELA DB2 18 Tue Mar 31, 2020 9:33 pm
No new posts DFSORT Output file order query A_programmers DFSORT/ICETOOL 2 Thu Mar 26, 2020 11:59 pm

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