On Fri, May 14, 2021 at 8:33 AM Pól Ua Laoínecháin <linehanp@tcd.ie> wrote:
I was able to do it by chaining CTEs - but I wanted to be sure that when chaining CTEs, all work done in a statement with multiple modifications to data was done within the same transaction - this is what I thought my SQL would do without using CTEs.
A statement will always execute as a single unit of work no matter how many individual sub-commands are buried within it. Additionally, if some of those commands modify data any given row of data can only be modified once within the statement. So you can't, e.g. do, with u1 as (update val = val + 2), u2 as (update val = val + 4)..., and expect the final output to have incremented val by 6, instead you will get a failure. So rows that are updated can only be updated once and those rows, if they are to appear in the final result, must be supplied to the rest of the statement via the returning clause, not by having other parts of the statement attempt to select those updated values from the original table.