I posted a related question, but this is another part of my puzzle.
I would like to get the OLD value of a column from a row that was UPDATEd - WITHOUT using triggers (nor stored procedures, nor any other extra, non -SQL/-query entities).
I have a query like this:
UPDATE my_table
SET processing_by = our_id_info -- unique to this worker
WHERE trans_nbr IN (
SELECT trans_nbr
FROM my_table
GROUP BY trans_nbr
HAVING COUNT(trans_nbr) > 1
LIMIT our_limit_to_have_single_process_grab
)
RETURNING row_id;
If I could do FOR UPDATE ON my_table
at the end of the subquery, that'd be divine (and fix my other question/problem). But that won't work: can't combine this with GROUP BY
(which is necessary for figuring out the count). Then I could just take those trans_nbr's and do a query first to get the (soon-to-be-) former processing_by
values.
I've tried doing like:
UPDATE my_table
SET processing_by = our_id_info -- unique to this worker
FROM my_table old_my_table
JOIN (
SELECT trans_nbr
FROM my_table
GROUP BY trans_nbr
HAVING COUNT(trans_nbr) > 1
LIMIT our_limit_to_have_single_process_grab
) sub_my_table
ON old_my_table.trans_nbr = sub_my_table.trans_nbr
WHERE my_table.trans_nbr = sub_my_table.trans_nbr
AND my_table.processing_by = old_my_table.processing_by
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by
But that can't work; old_my_table
is not visible outside the join; the RETURNING
clause is blind to it.
I've long since lost count of all the attempts I've made; I have been researching this for literally hours.
If I could just find a bullet-proof way to lock the rows in my subquery - and ONLY those rows, and WHEN the subquery happens - all the concurrency issues I'm trying to avoid would disappear ...
UPDATE: I had a typo in the non-generic code of the above. I retried after Erwin Brandstetter suggested it should work. Since it took me so long to find this sort of solution, perhaps my embarrassment is worth it? At least this is on SO for posterity now... :>
What I now have (that works) is like this:
UPDATE my_table
SET processing_by = our_id_info -- unique to this worker
FROM my_table AS old_my_table
WHERE trans_nbr IN (
SELECT trans_nbr
FROM my_table
GROUP BY trans_nbr
HAVING COUNT(*) > 1
LIMIT our_limit_to_have_single_process_grab
)
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
The COUNT(*)
is per a suggestion from Flimzy in a comment on my other (linked above) question.
Please see my other question for correctly implementing concurrency and even a non-blocking version; THIS query merely shows how to get the old and new values from an update, ignore the bad/wrong concurrency bits.
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
"The update() method is applied instantly and returns the number of rows affected by the query." The actual return value depends on the database backend. MySQL, for example, will always return 1 if the query is successful, regardless of the number of affected rows.
To do a conditional update depending on whether the current value of a column matches the condition, you can add a WHERE clause which specifies this. The database will first find rows which match the WHERE clause and then only perform updates on those rows.
The subquery defines an internal query that can be used inside a SELECT, INSERT, UPDATE and DELETE statement. It is a straightforward method to update the existing table data from other tables. The above query uses a SELECT statement in the SET clause of the UPDATE statement.
The CTE variant as proposed by @MattDiPasquale should work too.
With the comfortable means of a CTE I would be more explicit, though:
WITH sel AS (
SELECT tbl_id, name FROM tbl WHERE tbl_id = 3 -- assuming unique tbl_id
)
, upd AS (
UPDATE tbl SET name = 'New Guy' WHERE tbl_id = 3
RETURNING tbl_id, name
)
SELECT s.tbl_id AS old_id, s.name As old_name
, u.tbl_id, u.name
FROM sel s, upd u;
Without testing I claim this works: SELECT
and UPDATE
see the same snapshot of the database. The SELECT
is bound to return the old values (even if you place the CTE after the CTE with the UPDATE
), while the UPDATE
returns the new values by definition. Voilá.
But it will be slower than my first answer.
You can use a SELECT
subquery.
Example: Update a user's email RETURNING
the old value.
RETURNING
Subquery
UPDATE users SET email = '[email protected]' WHERE id = 1
RETURNING (SELECT email FROM users WHERE id = 1);
PostgreSQL WITH Query (Common Table Expressions)
WITH u AS (
SELECT email FROM users WHERE id = 1
)
UPDATE users SET email = '[email protected]' WHERE id = 1
RETURNING (SELECT email FROM u);
This has worked several times on my local database without fail, but I'm not sure if the SELECT
in WITH
is guaranteed to consistently execute before the UPDATE
since "the sub-statements in WITH are executed concurrently with each other and with the main query."
when faced with this dilemma I added junk columns to the table and then I copy the old values into the junk columns (which I then return) when I update the record. this bloats the table a bit but avoids the need for joins.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With