Is there a syntax error with this statement? It failed!

update CONTACT CNT set

CNT.ADDRESS_1_ID =
(
select
CA.CONTACT_ADDRESS_ID
from
CONTACT_ADDRESS CA
where
CA.CONTACT_ID = CNT.CONTACT_ID
)
where
CNT.ADDRESS_1_ID is null;

Thanks in advance,

:?
Newbie

See if this thread helps
Update Statment

Sathyaram,

Thanks as usual for the prompt reply. I did alter my statement to

Code:
update CONTACT CNT set ADDRESS_1_ID = (select CA.CONTACT_ADDRESS_ID from CONTACT_ADDRESS CA, CONTACT CNT where CA.CONTACT_ID = CNT.CONTACT_ID);

It gave me an

Error:
SQL0104N An unexpected token "set" was found following "<identifier>".
Expected tokens may include: "USING". SQLSTATE=42601

Any pointers? TIA.

~ Newbie

I think that is bit misleading ...

Try this:

update CONTACT CNT set ADDRESS_1_ID = (select CA.CONTACT_ADDRESS_ID from CONTACT_ADDRESS CA where CA.CONTACT_ID = CNT.CONTACT_ID);

Code:
update CONTACT CNT set ADDRESS_1_ID = (select CA.CONTACT_ADDRESS_ID from CONTACT_ADDRESS CA where CA.CONTACT_ID = CNT.CONTACT_ID);

did not work.

But
Code:
update CONTACT CNT set ADDRESS_1_ID = (select max(CA.CONTACT_ADDRESS_ID) from CONTACT_ADDRESS CA where CA.CONTACT_ID = CNT.CONTACT_ID);

worked, populating all the ADDRESS_1_ID column with same data (maximum of CONTACT_ADDRESS_ID ?! )

I know its weird, i am still curious if there was a way to dynamically update multiple records.

Thanks for all your help,

Newbie

Maybe I am wrong, but I thought there were some update enhancements in recent fixpaks. What release and fixpak are you on?

DB2 8.1.5 (fixpack 5) running on Windows 2000.

Is it possible? Any pointers in this regards would be really helpful.

Thanks,
Newbie