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
 

 

Upadate specific columns using Co-related Subquery

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

New User


Joined: 20 Sep 2007
Posts: 83
Location: India

PostPosted: Tue May 11, 2010 12:35 am    Post subject: Upadate specific columns using Co-related Subquery
Reply with quote

Hi,
I have a table TABLE1 that has 3 columns ID,AMT1,AMT3. The ID column is not unique. Now I want to add AMT1 & AMT2 values for each unique ID value in TABLE1 & populate it into a new table TABLE2. Thus TABLE2 will have unique ID value & AMT3 & AMT4 columns holding the sum of AMT1 & AMT2 respectively.
To explain with an example: Consider TABLE1 has the following 3 entries:
Code:
ID   AMT1   AMT2
100    5     4
100    4     2
200    3     9

After execution of the said query, TABLE2 will have the following entries:
Code:
ID   AMT3   AMT4
100    9     6
200    3     9

Also TABLE1 has many ID out of which we select a required few.
Please let me know if the following query will work:

Code:
UPDATE TABLE1 T1 SET (T1.AMT3,T1.AMT4) =
(
 SELECT VAL1,VAL2
 FROM (
       SELECT T2.ID,SUM(T2.AMT1) AS VAL1,SUM(T2.AMT2) AS VAL2
       FROM TABLE2 T2
       WHERE T2.ID IN (
                       SELECT T3.ID
                       FROM TABLE3 T3
                       WHERE T3.C_CODE = '300'
                      )
       GROUP BY T2.ID
      )
)
WHERE T1.ID = T2.ID


TABLE3 has unique values for ID.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue May 11, 2010 12:42 am    Post subject:
Reply with quote

Hello,

What happened when you tried this?

My intuition says this will not work. . .
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue May 11, 2010 11:57 am    Post subject:
Reply with quote

I'm pretty sure it won't work: can't use T2-fields in the outer where clause
and if it could it probaly would have a terrible response, but I won't even bother to try
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 83
Location: India

PostPosted: Tue May 11, 2010 3:38 pm    Post subject: Reply to: Upadate specific columns using Co-related Subquery
Reply with quote

Hi,

Sorry for the last reply.
This SQL threw an error as below:

Code:
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "<EMPTY>". SOME SYMBOLS THAT
         MIGHT BE LEGAL ARE: CORRELATION NAME


I believe this error is due to the reason that GUYC stated.

Please let me know if this can be handled using a query.

Thanks
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 83
Location: India

PostPosted: Tue May 11, 2010 3:41 pm    Post subject: Reply to: Upadate specific columns using Co-related Subquery
Reply with quote

last reply should have been late reply. icon_redface.gif
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 83
Location: India

PostPosted: Tue May 11, 2010 4:32 pm    Post subject: Reply to: Upadate specific columns using Co-related Subquery
Reply with quote

Hi,
Just another query.
Is there an way of selecting specific columns that I intend to update using an UPDATE query out of a list of columns. For example:

Code:
UPDATE TABLE1 SET(COL1,COL2) =
(
 SELECT COL3,COL4,COL5
 FROM TABLE2
 WHERE COL6 = '100'
)
WHERE COL1 > COL2


Here the inner SELECT has more than 2 columns listed. Out of them I intend to update the values of two columns only.
Is there a way of doing this?

Thanks
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue May 11, 2010 4:45 pm    Post subject:
Reply with quote

This should work :

Code:
UPDATE TABLE1 T1 SET (AMT3,AMT4) =
(SELECT SUM(T2.AMT1) ,SUM(T2.AMT2)
    FROM TABLE2 T2
   where T2.ID = T1.ID
)
WHERE exists (select 1 from table3 where T3.C_CODE = '300'  and T3.ID = T1.ID)

or
Code:
...
WHERE T1.ID IN (select T3.ID from table3 where T3.C_CODE = '300')


Exists or IN-list : what is faster depends on clustering, indexes and #qualifying rows.
Usually Exists is faster
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue May 11, 2010 4:47 pm    Post subject: Re: Reply to: Upadate specific columns using Co-related Subq
Reply with quote

Learncoholic wrote:

Here the inner SELECT has more than 2 columns listed. Out of them I intend to update the values of two columns only.
Is there a way of doing this?

No, SET (collist) = (updatecollist) must have same numbers of columns
why would you select more than you need ?
Back to top
View user's profile Send private message
Learncoholic

New User


Joined: 20 Sep 2007
Posts: 83
Location: India

PostPosted: Tue May 11, 2010 6:22 pm    Post subject: Reply to: Upadate specific columns using Co-related Subquery
Reply with quote

Hi GuyC,

The query you have supplied worked perfectly well as intended.
Thanks you very much.
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 unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Route a product's job to specific lpar vasanthz All Other Mainframe Topics 9 Thu Mar 02, 2017 2:22 am
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 Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts how to split records based on specifi... Venkata Ramayya DFSORT/ICETOOL 6 Wed Sep 28, 2016 3:20 am


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