Pancho Pancho - 28 days ago 14
SQL Question

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

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?

Answer
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