Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle SQL - can I return the "before" state of a column value

Tags:

sql

oracle

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?

like image 809
Pancho Avatar asked Nov 10 '16 08:11

Pancho


People also ask

How do I find previous row values in SQL?

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.

What is filter condition in Oracle?

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.

What is Rownum in Oracle SQL?

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.

What is lead and lag function in Oracle?

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.


1 Answers

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

like image 165
Mike Avatar answered Oct 24 '22 19:10

Mike