title: How do I use returning into clause in Oracle sql such that I can reuse the returning value to find a previous row?
body: <p>So I have a schema that looks something like this:</p>
  <pre><code>|ID | Date_of_transaction | Special_format_of_date_as_text | Data |
  </code></pre>
  <p>I ran across this piece of code where I update an existing row using a statement of the format:</p>
  <pre><code>UPDATE Data 
  WHERE Date_of_transaction = TRUNC(SPECIFICED_DATE) 
  returning special_format_of_date_as_text into date
  </code></pre>
  <p>BUT what I actually want is the previous week's special_format_of_date_as_text because the report title should reflect the previous week's activity and not the current week which has not happened yet.</p>
  <p>My question is how do I do that? I was trying to do an sub query for the returning clause using &quot;select special_format_of_date_as_text from table where Date_of_transaction = Date_of_transaction - 7&quot; but that didn't seem to work.</p>
  <p>My alternative would be to just return the date as is then create another query to get the previous date using a similar query I stated directly above.</p>
  <p>Example:</p>
  <pre><code>|ID | Date_of_transaction | Special_format_of_date_as_text | Data | Is_Updated |
  |1| 28-Aug-2020 | 2020Aug28DeltaAlpha | 23.42| Y |
  |2| 21-Aug-2020 | 2020Aug21DeltaAlpha | 0| N |
  |3| 14-Aug-2020 | 2020Aug14DeltaAlpha | 0| N |
  |4| 7-Aug-2020 | 2020Aug07DeltaAlpha | 0| N |
  </code></pre>
  <p>And I want to update row 2, but what I want to return is 2020Aug14DeltaAlpha because that's the week the report actually reflects and that's the title I will use for naming my report.</p>