Update multiple columns in a dimension table- Policy

Database

Update multiple columns in a dimension table- Policy

Hi Friends,

Please throw some light on the below update statement.

I need to update policy dimension table,  grain is policy number and begin date of the policy.

Example

Policy number           Begin date             End date          Ref_src_cd        Ref_src_desc

AB5656123              12/23/2009             4/15/9999       005                    Arrears

AB5656123              1/23/2008              12/22/2009       005                     Arrears

AB5656123               1/23/2007            1/22/2008         005                     Arrears

AB5656123               1/23/2006           1/22/2007          005                     Arrears

AB5656123               1/23/2005           1/22/2006          %                      

I need to update the policy record with value % with the value of the same ref_src_cd of the policy with the latest begin date (in this case with the first record) and update the ref_src_desc as well.

Below select statement will identify the records to be updated.

SELECT POL.POLICY_NBR, POL.BEGN_DT, POL.END_DT, POL.REF_SRC_CD, POL.REF_SRC_DESC

FROM POLICY POL

WHERE POL.BEGN_DT <= POL.END_DT

QUALIFY ROW_NUMBER() OVER ( PARTITION BY POL.POLICY_NBR ORDER  BY POL.BEGN_DT ASC) = 1

AND POL.REF_SRC_CD = '~'

REF source code and desc should be updated with the value retrieved from the below query

SELECT POL.POLICY_NBR, POL.BEGN_DT, POL.END_DT, POL.REF_SRC_CD, POL.REF_SRC_DESC

FROM POLICY POL

WHERE POL.BEGN_DT <= POL.END_DT

QUALIFY ROW_NUMBER() OVER ( PARTITION BY POL.POLICY_NBR ORDER BY POL.BEGN_DT ASC) = 1

AND POL.REF_SRC_CD <> '~'

Can you please help me with the update statement.