Re: UPDATE from CTE syntax error

On 05/26/2018 09:21 AM, Alexander Farber wrote:
> Good evening,
>
> I am struggling with the syntax, please help.
>
> This query with a CTE works ok:
>
> WITH
>
>              extract_letters AS (
>
>                                    SELECT
>
>                                                          mid,
>
>
>  STRING_AGG(x->>'letter', '') AS letters
>
>         FROM (
>
>                                SELECT
>
>                                                      mid,
>
>
>  JSONB_ARRAY_ELEMENTS(tiles) AS x
>
>        FROM words_moves
>
>                      WHERE action='play'
>
>                             ) z
>
> GROUP BY mid)
>
>       SELECT * from extract_letters;
>
>  mid   | letters
> --------+---------
>      12 | АКЖОЛ
>      15 | ДМО
>      16 | ТО
>      20 | ШГА
>      21 | КТИ
>      22 | ВОЗ
>      24 | АКПОНК
>
> But UPDATEing from the CTE does not -
>
> WITH extract_letters AS (
>
>                       SELECT
>
>                                              mid,
>
>
>  STRING_AGG(x->>'letter', '') AS letters
>
>         FROM (
>
>                                SELECT
>
>                                                      mid,
>
>
>  JSONB_ARRAY_ELEMENTS(tiles) AS x
>
>        FROM words_moves
>
>                      WHERE action='play'
>
>                             ) z
>
>                                   GROUP BY mid)
>
>          UPDATE words_moves m
>
>                        SET m.letters = el.letters

I would say the above is the problem:

https://www.postgresql.org/docs/10/static/sql-update.html
"column_name

The name of a column in the table named by table_name. The column
name can be qualified with a subfield name or array subscript, if
needed. Do not include the table's name in the specification of a target
column — for example, UPDATE table_name SET table_name.col = 1 is invalid.
"

So it should be:

SET letters = el.letters

>
>                                      FROM extract_letters el
>
>                                            WHERE m.mid = el.mid;
>
> ERROR:  42703: column "m" of relation "words_moves" does not exist
> LINE 14: SET m.letters = el.letters
>              ^
>
> Regards
> Alex

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com