Re: RETURNING, CTEs and TRANSACTION ISOLATION levels...

On Fri, May 14, 2021 at 8:33 AM Pól Ua Laoínecháin <linehanp(at)tcd(dot)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.

David J.