title: SQL-Oracle Update table from itself using LAG()
body: <p>Lets assume we have a table like this one:</p>
  
  <pre><code>| Year | Month | Day | ID_Office | Operations | Operations yesterday |
  |------|-------|-----|-----------|------------|----------------------|
  | 2016 | 12    | 31  | 9555      | 500        | 0                    |
  | 2017 | 1     | 1   | 9555      | 600        | 0                    |
  | 2017 | 1     | 2   | 9555      | 750        | 0                    |
  | 2017 | 1     | 3   | 9556      | 800        | 0                    |
  </code></pre>
  
  <p>I would like to update the value from "Operations yesterday" using "Operations" from the previous row. So the result table must be like this one:</p>
  
  <pre><code>| Year | Month | Day | ID_Office | Operations | Operations yesterday |
  |------|-------|-----|-----------|------------|----------------------|
  | 2016 | 12    | 31  | 9555      | 500        | 0                    |
  | 2017 | 1     | 1   | 9555      | 600        | 500                  |
  | 2017 | 1     | 2   | 9555      | 750        | 600                  |
  | 2017 | 1     | 3   | 9556      | 800        | 0                    |
  </code></pre>
  
  <p>I'm using the following code with function LAG(), but it is not updating the correct value.</p>
  
  <pre><code>Update table1 F1
  Set f1.operations_yesterday = 
                         (Select LAG(f1.operations, 1, 0) OVER(ORDER BY year, month, day)
                          From table1 F2
                          Where 
                          F1.year= F2.year And
                          F1.month= F2.month And
                          F1.day= F2.day);
  </code></pre>
  
  <p>Could you give me some advices from my code about what is exactly wrong?
  I had success with lag() function on"Insert Into" statements, but in this case I must provide an Update statement solution.</p>
  
  <p>NOTE:
  Note that there could be more than three fields to be ordered by. I put as anexample only Year, Month and Day, but there are more fields not related to dates.
  Also, it is important to detail that not everyday has values.</p>
  
  <p>Thank you in advance!</p>