表关联更新语句

场景：

表：

test_substation

test_SUBSTATION_1

 

更新栏位：

EMP_ID, APPROVER_SEQUENCE, APPROVER_ID

 

关联栏位：

EMP_ID,  APPROVER_ID

 

方法一：

UPDATE test_substation a SET (a.ZONE_ID, a.STATUS, a.PWMIS_CODE) = (SELECT b.MANAGE_REGION, b.RUN_STATE, b.PMMIS_CODE FROM test_SUBSTATION_1 b WHERE b.sys_id = a.sys_id) WHERE EXISTS (SELECT 1 FROM test_SUBSTATION_1 c WHERE c.sys_id = a.sys_id)

 

 

方法二：(10g(含)以上版本才可以使用)

MERGE INTO gt$fm_form_field_import imp
         USING fm_form_field fie
         ON (    imp.field_name = fie.field_name
             AND fie.form_kind = 'BQE.FORM.7'
             AND fie.table_name = 'BQEFORM7')
         WHEN MATCHED THEN
            UPDATE
               SET imp.description = fie.description,
                   imp.is_query = fie.is_query,
                   imp.is_default_value = fie.is_default_value,
                   imp.is_query_show = fie.is_query_show,
                   imp.is_form_show = fie.is_form_show,
                   imp.is_participant = fie.is_participant,
                   imp.is_mail_show = fie.is_mail_show

 

转载于:https://www.cnblogs.com/gzwain/archive/2009/07/20/1526811.html