How Can I Retrieve Pre-Update Column Values in SQL Without Using Triggers or Stored Procedures?

Retrieving Pre-Update Values using SQL ONLY

In a scenario where triggers, stored procedures, or external entities are unavailable, it becomes necessary to retrieve the old values of a column from a row that has been updated using SQL alone.

The Challenge

Retrieving pre-update values directly from the RETURNING clause is not possible as it only returns the post-update values.

Solution using a Self-Join

The solution is to join the table to another instance of itself using a condition that uniquely identifies the row being updated. This allows access to both the old and new values in the RETURNING clause.

UPDATE my_table
SET processing_by = our_id_info
FROM my_table AS old_my_table
WHERE old_my_table.trans_nbr = my_table.trans_nbr
AND my_table.row_id = old_my_table.row_id
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by AS old_processing_by

Handling Concurrency

To avoid potential race conditions, it's crucial to prevent concurrent writes to the same rows during the update. This can be achieved using an explicit lock on the row to be updated:

UPDATE my_table
SET processing_by = our_id_info
FROM (SELECT * FROM my_table WHERE trans_nbr = 4 FOR UPDATE) AS old_my_table
WHERE old_my_table.trans_nbr = my_table.trans_nbr
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by AS old_processing_by

This ensures that only the rows locked by the subquery are processed, preventing conflicting updates.

The above is the detailed content of How Can I Retrieve Pre-Update Column Values in SQL Without Using Triggers or Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!