Assume the following row in myTable:
id = 1
letter = 'a'
In Oracle, one can easily do the following:
update myTable set
letter = 'b'
where id = 1
returning letter
into myVariable;
and myVariable will then hold the value 'b'.
What I am looking for is some way of returning the "before" value of letter
ie. replace the previous update with:
update myTable set
letter = 'b'
where id = 1
returning letter "before the update"
into myVariable;
and myVariable should then hold the value 'a';
I understand that T-SQL can achieve this via the OUTPUT clause.
Is there an Oracle equivalent way of achieving this so I don't have to first do a "select" just to get the before value?
SQL Server LAG() is a window function that provides access to a row at a specified physical offset which comes before the current row. In other words, by using the LAG() function, from the current row, you can access data of the previous row, or the row before the previous row, and so on.
Oracle Expression Filter, a feature of Oracle Database 10g, is a component of Rules Manager that allows application developers to store, index, and evaluate conditional expressions (expressions) in one or more columns of a relational table. Expressions are a useful way to describe interests in expected data.
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
Description. The Oracle/PLSQL LAG function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table. To return a value from the next row, try using the LEAD function.
update
(
select T.*, (select letter from DUAL) old_letter
from myTable T
where id=1
)
set letter = 'b'
returning old_letter into myVariable;
Tested on Oracle 11.2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With