|
View previous topic :: View next topic
|
| Author |
Message |
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
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 |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
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  |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
thx rocky_balboa,
appreciate the critique of my poor sql.
do you have any suggestions for the TS and his problem? |
|
| Back to top |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
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 |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
| missed SET |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
thx again rocky_balboa,
i'll test it on monday, and redo my research on CTE |
|
| Back to top |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
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 |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
| Sorry, the query would a catastrophe.......will update all the rows in FIRST.TABLEA.... weekend laziness... |
|
| Back to top |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
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 |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| 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 |
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 |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
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 |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
| 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 |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
dejunzhu
CTE - Common Table Expression
NTE - Nested Table Expression |
|
| Back to top |
|
|
|
|
