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

Retrieving Pre-Update Column Values Solely Using SQL

Retrieving the original values of columns in a row after an update without employing triggers, stored procedures, or other external entities is a recurring challenge in SQL. To address this, we explore different approaches.

Initial Attempts and Drawbacks

As noted in the original question, using FOR UPDATE with a subquery containing a GROUP BY clause is not feasible. Joins consequently become unworkable.

Solution Using a Table Alias

The most straightforward solution is to join the table with itself using a table alias. By aliasing the table, we can access both the original and updated values. Consider the following update query:

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

Handling Concurrent Write Operations

To prevent concurrent write operations from affecting the result, we can utilize the FOR UPDATE clause in a subquery. This ensures that only the row locked by the subquery is processed.

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;

Other Considerations

• The columns used for the self-join must be unique and not null.
• It is crucial to accurately capture the concurrency requirements of the application.
• Using the SERIALIZABLE isolation level can ensure reliable concurrent updates at the expense of performance.
• Alternatively, explicit locking using FOR UPDATE can provide a balance between reliability and performance.

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