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
 

 

To update the rows in 2 tables

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

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Sat May 14, 2011 1:32 am    Post subject: To update the rows in 2 tables
Reply with quote

Hi All,

I have 2 tables (Table A and B) and need to update the rows of Table B alone based on the matching fields of Table A.

Table A has
Code:
Acct ID    Policy No   Pol Eff Dt      Dist Code  Region No
1000        11            2001-01-01     A              150
2000        21            2002-01-01     A              250
3000        31            2003-01-01     A              250
4000        41            2004-01-01     A              150
5000        51            2005-01-01     A              250

Table B has
Code:
Acct ID    Policy No   Pol Eff Dt      Dist Code 
1000        11            2001-01-01     B
1500        12            2001-06-01     C             
2000        21            2002-01-01     B           
4000        41            2004-01-01     B       


If the Account / Policy number / Pol Effective date combination from Table B is matching with those of Table A, then update the Dist code of Table B as 'A'

Output of Table B should be
Code:
Acct ID    Policy No   Pol Eff Dt      Dist Code 
1000        11            2001-01-01     A
1500        12            2001-06-01     C             
2000        21            2002-01-01     A           
4000        41            2004-01-01     A     



I tried joining both the tables for SELECT however got confuseed when doing update. Could anyone of give some guidance here ?

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

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat May 14, 2011 3:10 pm    Post subject:
Reply with quote

couple of questions:
do you want to update the dist_code of table_b to that of table_a
or hard code a literal 'A' when dist_code of a = 'A'?

i will assume you want to make table_b dist_code = table_a dist_code
otherwise add a where clause in the select within the WITH to only look for 'A' as dist_code of table_a


probably better ways,
and i don't know if this will work,
and don't know your db2 level,
but,
there have been several posts by the db2 guru's using the WITH clause,
so, suggestion, (you will have to work out the syntax, don't have db2 available right now to test):

Code:

with tabj(select a.acct_id
               , a.policy
               , a.pol_eff_dt
               , a.dist_code
           from table_a a
           join table_b b
             on a.acct_id     = b.acct_id
            and a.policy      = b.policy
            and a.pol_eff_dt  = b.pol_eff_dt
         ) as d
update table_a c
set c.dist_code = d.dist_code
where c.acct_id    = d.acct_id
  and c.policy     = d.policy
  and c.pol_eff_dt = d.pol_eff_dt
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Sat May 14, 2011 8:06 pm    Post subject:
Reply with quote

Dick
The CTEs can be used only in SELECT, CREATE VIEW, INSERT or RETURN statements and not in UPDATE....should throw a syntax error... Someone correct me if I am wrong.

By the way, you have mixed up the syntaxes for CTE and NTE....
'tabj' is not referenced anywhere icon_smile.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat May 14, 2011 8:23 pm    Post subject:
Reply with quote

thx rocky_balboa,

appreciate the critique of my poor sql.

do you have any suggestions for the TS and his problem?
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Sat May 14, 2011 9:43 pm    Post subject:
Reply with quote

UPDATE FIRST.TABLEA FIELD1 = (SELECT FIELD1 FROM
SECOND.TABLEB AS B WHERE
B.FIELD2= FIRST.TABLEA.FIELD2
AND B.FIELD3= FIRST.TABLEA.FIELD3
AND B.FIELD4= FIRST.TABLEA.FIELD4)

Assumption: FIRST.TABLEA is the child of SECOND.TABLEB and FIELD1 is 'NOT NULLABLE' in SECOND.TABLEB
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Sat May 14, 2011 10:29 pm    Post subject:
Reply with quote

missed SET
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun May 15, 2011 12:58 am    Post subject:
Reply with quote

thx again rocky_balboa,

i'll test it on monday, and redo my research on CTE
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Sun May 15, 2011 9:48 am    Post subject:
Reply with quote

Updated one -

UPDATE FIRST.TABLEA SET FIELD1 = (SELECT FIELD1 FROM
SECOND.TABLEB AS B, FIRST.TABLEA AS A WHERE
B.FIELD2= A.FIELD2
AND B.FIELD3= A.FIELD3
AND B.FIELD4= A.FIELD4)
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Sun May 15, 2011 10:19 am    Post subject:
Reply with quote

Sorry, the query would a catastrophe.......will update all the rows in FIRST.TABLEA.... weekend laziness...
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Sun May 15, 2011 10:46 am    Post subject:
Reply with quote

This should work. Correlation was missing in my previous query.....

UPDATE TAB1 A

SET A.FIELD1 = (SELECT FIELD1 FROM
TAB2 WHERE
A.FIELD2=FIELD2
AND A.FIELD3= FIELD3
AND A.FIELD4= FIELD4)

WHERE EXISTS(SELECT 1 FROM TAB2 WHERE A.FIELD2=FIELD2
AND A.FIELD3= FIELD3
AND A. FIELD4= FIELD4)
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Mon May 16, 2011 6:40 am    Post subject:
Reply with quote

rocky_balboa wrote:
Dick
The CTEs can be used only in SELECT, CREATE VIEW, INSERT or RETURN statements and not in UPDATE....should throw a syntax error... Someone correct me if I am wrong.

By the way, you have mixed up the syntaxes for CTE and NTE....
'tabj' is not referenced anywhere icon_smile.gif

I'm sorry, but would you please kindly tell me what CTE/NTE mean here?
I googled, but no expected answer.

Thanks for your reply.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Mon May 16, 2011 9:47 am    Post subject:
Reply with quote

Thanks all for the answers.
I will do my testing on Monday and will let you know about the results.

Dick - I want o hardcode the literal 'A' in Table B when the condition meets (when Acct ID / Policy ID / Pol Eff date of both the table matches).

Rocky - Both table A and B doesn't have any relation between each other.

Appreciate your help.

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

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Mon May 16, 2011 10:47 am    Post subject:
Reply with quote

Quote:
Both table A and B doesn't have any relation between each other


When I say 'FIRST.TABLEA is the child of SECOND.TABLEB' I meant figuratively.... that all the rows which you want to update in 'FIRST.TABLEA' should have matching rows in 'SECOND.TABLEB' .... else you have to handle the 'null' returned from your reference table for non matching rows....SECOND.TABLEB in my case...
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Mon May 16, 2011 10:54 am    Post subject:
Reply with quote

dejunzhu

CTE - Common Table Expression
NTE - Nested Table Expression
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 -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 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 How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm


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