Return Pre-Update Column Values Using SQL Only

Return pre-UPDATE column values using SQL only

Problem

The manual explains:

The optional RETURNING clause causes UPDATE to compute and return
value(s) based on each row actually updated. Any expression using the
table's columns, and/or columns of other tables mentioned in FROM, can
be computed. The new (post-update) values of the table's columns are
used. The syntax of the RETURNING list is identical to that of the
output list of SELECT.

Bold emphasis mine. There is no way to access the old row in a RETURNING clause. You can work around this restriction with a trigger or a separate SELECT before the UPDATE wrapped in a transaction or wrapped in a CTE as was commented.

However, what you are trying to achieve works perfectly fine if you join to another instance of the table in the FROM clause:

Solution without concurrent writes

UPDATE tbl x
SET    tbl_id = 23
     , name = 'New Guy'
FROM   tbl y                -- using the FROM clause
WHERE  x.tbl_id = y.tbl_id  -- must be UNIQUE NOT NULL
AND    x.tbl_id = 3
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;

Returns:

 old_id | old_name | tbl_id |  name
--------+----------+--------+---------
  3     | Old Guy  | 23     | New Guy

The column(s) used to self-join must be UNIQUE NOT NULL. In the simple example, the WHERE condition is on the same column tbl_id, but that's just coincidence. Works for any conditions.

I tested this with PostgreSQL versions from 8.4 to 13.

It's different for INSERT:

• INSERT INTO ... FROM SELECT ... RETURNING id mappings

Solutions with concurrent write load

There are various ways to avoid race conditions with concurrent write operations on the same rows. (Note that concurrent write operations on unrelated rows are no problem at all.) The simple, slow and sure (but expensive) method is to run the transaction with SERIALIZABLE isolation level:

BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE ... ;
COMMIT;

But that's probably overkill. And you need to be prepared to repeat the operation in case of a serialization failure.

Simpler and faster (and just as reliable with concurrent write load) is an explicit lock on the one row to be updated:

UPDATE tbl x
SET    tbl_id = 24
     , name = 'New Gal'
FROM  (SELECT tbl_id, name FROM tbl WHERE tbl_id = 4 FOR UPDATE) y 
WHERE  x.tbl_id = y.tbl_id
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;

Note how the WHERE condition moved to the subquery (again, can be anything), and only the self-join (on UNIQUE NOT NULL column(s)) remains in the outer query. This guarantees that only rows locked by the inner SELECT are processed. The WHERE conditions might resolve to a different set of rows a moment later.

See:

• Atomic UPDATE .. SELECT in Postgres

db<>fiddle here

_{Old sqlfiddle}

Is it possible (how?) to return old value from after update trigger

Yes it is, but no triggers are involved here at all. Just a plain UPDATE.

The RETURNING clause returns values based on the row after the update. But you can include the old row in a FROM clause and use that. Per documentation:

Any expression using the table's columns, and/or columns of other tables mentioned in FROM, can be computed. The new (post-update) values of the table's columns are used.

Solution:

UPDATE "Users" x
SET    "displayName" = 'new_name' 
FROM  (SELECT id, "displayName" FROM "Users" WHERE id = 1 FOR UPDATE) y 
WHERE  x.id = y.id
RETURNING y."displayName"

Detailed explanation:

• Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version

What if my ORM does not allow a FROM clause?

Are you sure? If so, a simple (more expensive) alternative would be to run two statements:

BEGIN;
-- Retrieve old value first;
SELECT "displayName" FROM "Users" WHERE id = 1 FOR UPDATE;

UPDATE "Users"
SET    "displayName" = 'new_name' 
WHERE  id = 1;

COMMIT;

The transaction wrapper (BEGIN; ... COMMIT;) and the lock on the row (FOR UPDATE) are to defend against possible concurrent writes. You can remove both if you are the only one writing to that table or if an occasional outdated "old value" is ok. Rarely happens except in very competitive setups.

Or use raw SQL to bypass your inferior ORM.

I can imagine tricks to work around this (like adding a redundant column with the pre-update value of "displayName"), but such trickery sounds like a very bad idea. Just use the standard functionality.

UPDATE column with previous value

update mytable t set myval=(select s.myval from mytable s where s.myid < t.myid and s.myval!=0 order by s.myid desc limit 1) where t.myid in (select myid from mytable where myval=0 order by myid for update) ;

results

select * from mytable;
 myid | myval
------+-------
    1 |     1
    2 | 0.123
    3 |     0
    4 |     5
    7 |     0

update mytable t set myval=(select s.myval from mytable s where s.myid < t.myid and s.myval!=0 order by s.myid desc limit 1) where t.myid in (select myid from mytable where myval=0 order by myid for update) ;

select * from mytable order by myid;
 myid | myval
------+-------
    1 |     1
    2 | 0.123
    3 | 0.123
    4 |     5
    7 |     5

How to know which column has changed on UPDATE?

Basically, you need the pre-UPDATE values of updated rows to compare. That's kind of hard as RETURNING only returns post-UPDATE state. But can be worked around. See:

• Return pre-UPDATE column values using SQL only

So this does the basic trick:

WITH input(col1, col2) AS (
   SELECT 1, text 'post_up'  -- "whole row"
   )
, pre_upd AS (
   UPDATE tab1 x
   SET    (col1, col2) = (i.col1, i.col2)
   FROM   input i
   JOIN   tab1  y USING (col1)
   WHERE  x.col1 = y.col1
   RETURNING y.*
   )
TABLE  pre_upd
UNION ALL
TABLE  input;

db<>fiddle here

This is assuming that col1 in your example is the PRIMARY KEY. We need some way to identify rows unambiguously.

Note that this is not safe against race conditions between concurrent writes. You need to do more to be safe. See related answer above.

The explicit cast to text I added in the CTE above is redundant as text is the default type for string literals anyway. (Like integer is the default for simple numeric literals.) For other data types, explicit casting may be necessary. See:

• Casting NULL type when updating multiple rows

Also be aware that all updates write a new row version, even if nothing changes at all. Typically, you'd want to suppress such costly empty updates with appropriate WHERE clauses. See:

• How do I (or can I) SELECT DISTINCT on multiple columns?

While "passing whole rows", you'll have to check on all columns that might change, to achieve that.

Update column value to be minimum of all column values before it

You could try cumulative minimum function in teradata.

Select Store, Product_ID, Week_Number, Units,
MIN(Units) over (PARTITION BY Store, Product_ID ORDER BY Week_Number ROWS UNBOUNDED PRECEDING) as Corrected_units from TABLE_NAME;

How to return columns before update using returning?

No, returning will give you the column values that result after the update. So better select the column values before you update.

select s.column1,
       s.column2
into v_column1,v_column2
from cards s
where s.column3= in_column3;

SQL Server - Is it possible to return an existing column value before a row update?

Try the OUTPUT clause:

declare @previous table(newscore int, Oldscore int, postFK int, userFK int)

UPDATE ForumVotes 
SET score = @newVote 
OUTPUT inserted.score,deleted.score, deleted.postFK, deleted.userFK into @previous
WHERE postFK = @postID
AND userFK = @userID

select * from @previous

SQL - Update query only update one field

You can use cross apply to define the new quantity in the FROM clause, and then use that value:

Update s
    set Quantity = v.Quantity,
        Total = v.Quantity * s.PPrice
    from tblStock s cross apply
         (values (s.Quantity + 10)) v(quantity)
    where s.barcode = 'shandar';

Repeating a simple operation such as quantity + 10 isn't a big deal. However, if the calculation is more complex, it is simpler to do it once.

That said, you might be better off with total as a computed column. Then it is just correct -- and never has to be updated. Drop it from the table and then re-add it as:

alter table tblStock add total as ( quantity * pprice );

Then the value of total is always correct, because it is calculated when you query the table.

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

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

---

Related Topics