IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

To update the rows in 2 tables


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Sat May 14, 2011 1:32 am
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
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
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
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
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
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
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
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
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
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
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: 179
Location: India

PostPosted: Mon May 16, 2011 9:47 am
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Reflection 3270 screen shows only 23 ... Compuware & Other Tools 6
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
No new posts Convert single row multi cols to sing... DFSORT/ICETOOL 6
No new posts Compare latest 2 rows of a table usin... DB2 1
Search our Forums:

Back to Top