Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 1714
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: 1714
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: 331
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: 1609
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Matching with Key at different postions. rajatbagga DFSORT/ICETOOL 12 Wed Nov 09, 2016 10:58 am
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us