Databases
StackOverflow2013
Postsdbo
POReturn pre-UPDATE Column Values Using SQL Only - PostgreSQL Version
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POReturn pre-UPDATE Column Values Using SQL Only - PostgreSQL Version
    primarykey
    data
    text
    <p><a href="https://stackoverflow.com/q/7922211/902825">I have a related question</a>, but this is another part of MY puzzle.</p> <p>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).</p> <p>The query I have is like this:</p> <pre><code> 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) &gt; 1 LIMIT our_limit_to_have_single_process_grab ) RETURNING row_id; </code></pre> <p>If I could do "FOR UPDATE ON my_table" at the end of the subquery, that'd be devine (and fix my other question/problem). But, that won't work: can't have this AND a "GROUP BY" (which is necessary for figuring out the COUNT of trans_nbr's). Then I could just take those trans_nbr's and do a query first to get the (soon-to-be-) former processing_by values.</p> <p>I've tried doing like:</p> <pre><code> 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) &gt; 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 </code></pre> <p>But that can't work; <code>old_my_table</code> is not visible outside the join; the <code>RETURNING</code> clause is blind to it.</p> <p>I've long since lost count of all the attempts I've made; I have been researching this for literally hours.</p> <p>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 ...</p> <hr> <p><strong>UPDATE:</strong> [WIPES EGG OFF FACE] Okay, so I had a typo in the non-generic code of the above that I wrote "doesn't work"; it does... thanks to <strong>Erwin Brandstetter</strong>, below, who stated it would, I re-did it (after a night's sleep, refreshed eyes, and a banana for bfast). Since it took <em>me</em> so long/hard to find this sort of solution, perhaps my embarrassment is worth it? At least this is on SO for posterity now... :></p> <p>What I now have (that works) is like this:</p> <pre><code> 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(*) &gt; 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 </code></pre> <p>The <strong>COUNT(*)</strong> is per a suggestion from <strong>Flimzy</strong> in a comment on my other (linked above) question. (I was more specific than necessary. [In this instance.])</p> <p><a href="https://stackoverflow.com/q/7922211/902825">Please see my other question</a> 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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USpythonlarry
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POReturn pre-UPDATE Column Values Using SQL Only - PostgreSQL Version
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POReturn pre-UPDATE Column Values Using SQL Only - PostgreSQL Version
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POReturn pre-UPDATE Column Values Using SQL Only - PostgreSQL Version
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhy can't you use a rule or trigger?
      singulars
      1. POReturn pre-UPDATE Column Values Using SQL Only - PostgreSQL Version
      1. USFlimzy
    2. COBest way IMHO is to make the old rows historic, either by explicit SQL, or by a rewrite rule or trigger.
      singulars
      1. POReturn pre-UPDATE Column Values Using SQL Only - PostgreSQL Version
      1. USwildplasser
    3. CO@Flimzy: 1. If one didn't have access to such things (though *I* do), if it CAN be done purely in SQL/single query... 2. Rules/triggers are a whole 'nother debug enchilada. 3. Keeping it simple, straight SQL, and having One Query To Do It All does K.I.S.S. nicely. Thanks, again, for the count(*) reminder, tho! :>
      singulars
      1. POReturn pre-UPDATE Column Values Using SQL Only - PostgreSQL Version
      1. USpythonlarry
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload