Oracle Update using Lag function
<p>I am trying to use a <code>lag</code> function to <code>update</code> effective start dates in a <code>SCD2</code> dimension.  I am trying to use a subquery to self join the table based on the PK.  The update will not update based on the previous end date and instead will only update to the default value (which is itself).  When I remove the default value I get an error because the effective start date cannot be null.  When I just use the <code>select</code> I get the desired result. </p>

<p>Any assistance would be greatly appreciated, I'm sure its something simple!</p>

<pre><code>update schema.d_account ac1
set effective_start_dt = (select lag(effective_end_dt, 1, effective_start_dt) over(partition by id order by effective_end_dt asc) 
                          from schema.d_account ac2
                          where ac1.account_dim_key = ac2.account_dim_key),
audit_last_update_dt = sysdate,
where id in '0013600000USKLqAAP'
</code></pre>

<p>Table:</p>

<p><a href="https://i.stack.imgur.com/9ZFFP.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9ZFFP.jpg" alt="enter image description here"></a></p>

<p>Desired results:</p>

<p><a href="https://i.stack.imgur.com/HeKp9.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/HeKp9.jpg" alt="enter image description here"></a></p>