looks like a simple case but I'm confused:

table_A column - date_A , primary key key_A
table_B column - date_B, foreign key key_B

I need to update every date_A with date_B.

Update table_A
set date_A =
(select date_B
from ?
where ?
)

Thanks for your help.

http://www.dbforums.com/t999150.html

From the link to the other thread I was still unable to find solution.

I need to update each row of table_A with matching row from table_B.
Updated column is date_A of table_A. It needs to take a value of date_B of table_B. Primary key is key_A and key_B.

Thank you.

Lenny,

that case does not really makes sense:

> need to update every date_A with date_B.

Update table_A
set date_A =
(select date_B
from table_B
where table_A. date_A = table_B.date_B
)

means that column A is already the value of column B. So what do you want to do exactly? What is the correlation criteria for the update?

cheers
fynn

Fynn / Lenny,

Code:

Update table_A
set date_A =
(select date_B
from table_B
where table_A. date_A = table_B.date_B
)

would not work. I guess there is no dynamic mass update statement available.

BUT Code:

Update table_A
set date_A =
(select MAX(date_B)
from table_B
where table_A. date_A = table_B.date_B
)

would work because "select MAX(date_B) from table_B" returns one record, where as "select date_B from table_B" returns many records.

I am still trying to figure out a way to do that. You might want to refer the DB2 cookbook. There are many examples available.

Newbie

Fynn,
dsusendran is right on the money with my problem.
I don't think that code
Update table_A
set date_A =
(select MAX(date_B)
from table_B
where table_A. date_A = table_B.date_B
)

would serve my need.
I am trying to update date from one table with date from the other table.
This needs to be done thru correlated subquery which would do it for each row. However I am not sure how.

Thank you all.

create table table_A(keycol int,date_A date) ;

create table table_B(keycol int,date_B date) ;

select * from table_a
1 2004-05-01
2 2004-05-02
10 2004-05-10

select * from table_b

2 2005-05-02
1 2005-05-01
3 2005-05-03

update table_a a set date_a=(select date_b from table_b b where a.keycol=b.keycol)
where exists (select 1 from table_b c where a.keycol=c.keycol)

select * from table_A

1 2005-05-01
2 2005-05-02
10 2004-05-10


If you exclude the exisits clause (update table_a a set date_a=(select date_b from table_b b where a.keycol=b.keycol)
)

select * from table_a

1 2005-05-01
2 2005-05-02
10 -



Based on your requirement, you can choose one of the above statements ...

Isn't this you want ?????

If not, tell us what the resultset should be ...

This query is the same as the one in the post I have referenced above

Cheers
Sathyaram

Update table_A
set date_A = date_B


This will update all the data in date_A with data in date_B.

MN

@all

my post was to show that the query would not work, it wasn't a real suggestion.

Famuda, your sample even does not make really sense because there is no corrleation (join) criteria. So Lenny, what excactly do you want to do?
If the challenge is just to copy all values of column B to column A you are right, but is this really the requirement?

Please submit more information about your aim.

Is there a matching row in table B for every single row in table A? How do you match them? If you use the date as matching criteria then table A is already in the right state.


cheers
fynn

Fynn,
I'm with you 100% about Famudba's post. I don't mean to be *** about it but it may confuse others who's trying to search before posting their problem.

Date is not part of matching in my case.

Here it is:

UPDATE DBQ2.DB2DBA.ELC_PKG A
SET DTE_ELC_END =
(SELECT B.VEUC_END_DATE FROM DBQ2.DB2DBA.V3CRVEUC B,
DBQ2.DB2DBA.ELC_PKG A
WHERE A.CDE_WRLD_MFR = B.VEUC_W_MANUFACT_CD
AND A.NUM_BAUM_1_3 = B.VEUC_FIN_BAUREIHE
AND A.NUM_BAUM_4_6 = B.VEUC_FIN_DIGIT_4_6
AND A.CDE_FIN_STR_PLANT = B.VEUC_FIN_DIGIT_7_8
AND A.NUM_VEH_SERIAL = B.VEUC_FIN_SERIAL_NO
AND B.VEUC_F_ACTIVE = '1'
AND A.CDE_ELC_STAT = '006'
AND SUBSTR(A.NAM_ELC_MBUSA,1,4) = SUBSTR(B.VEUC_CD,1,4)
AND A.DTE_ELC_END ¬= B.VEUC_END_DATE
AND B.VEUC_END_DATE > CURRENT DATE)
-- AND EXISTS
WHERE EXISTS
(SELECT 1 FROM DBQ2.DB2DBA.V3CRVEUC C
WHERE A.CDE_WRLD_MFR = C.VEUC_W_MANUFACT_CD
AND A.NUM_BAUM_1_3 = C.VEUC_FIN_BAUREIHE
AND A.NUM_BAUM_4_6 = C.VEUC_FIN_DIGIT_4_6
AND A.NUM_BAUM_4_6 = C.VEUC_FIN_DIGIT_4_6
AND A.CDE_FIN_STR_PLANT = C.VEUC_FIN_DIGIT_7_8
AND A.NUM_VEH_SERIAL = C.VEUC_FIN_SERIAL_NO
AND C.VEUC_F_ACTIVE = '1'
AND A.CDE_ELC_STAT = '006'
AND SUBSTR(A.NAM_ELC_MBUSA,1,4) = SUBSTR(C.VEUC_CD,1,4)
AND A.DTE_ELC_END ¬= C.VEUC_END_DATE
AND C.VEUC_END_DATE > CURRENT DATE
);


returns -811 (multiple rows)

I thank you all for your help. This is graet thing forum like this one exists