Problem when updating table 1 based on table2

[john9]

Problem when updating table 1 based on table2

I am trying to update a table using information from
another table. As shown below when I tried to update
table ''commdev.comm_policy_activity_stg2'' I am receiving
a expression error syntax message. I'm not sure what is
missing. I would appreciate help from more experienced
SQL developers.

Thanks.
=========================================================
update commdev.comm_policy_activity_stg2
set pol_company_extension_name = co_trans_co_extension
(select co_trans_co_extension from
commdev.comm_company_code_translation
where pol_orig_system = co_trans_orig_system
and pol_company = co_trans_company)

where pol_record_type = '10';

 

[srishan]

Here is a sample, hope you can construct your SQL from this,

UPDATE table1 T1
SET T1.col1 = (SELECT col2 FROM table2 T2
               WHERE T2.colx = T1.colx
               AND T2.coly = T2.coly)
WHERE T1.colz = 'hard coded value'
AND EXISTS (SELECT 'X' FROM table2 T2
            WHERE T2.colx = T1.colx
            AND T2.coly = T2.coly);

Thx,
SriDHAR