Re: Writeable CTE Not Working?

2013/1/29 Kong Man <kong_mansatiansin@hotmail.com>:
> Can someone explain how this writable CTE works?  Or does it not?

They surely do, I use this feature a lot.
Take a look at the description in the docs:
http://www.postgresql.org/docs/current/interactive/queries-with.html#QUERIES-WITH-MODIFYING


> WITH upd_code AS (
>   UPDATE suppliers SET suppliercode = NULL
>   WHERE suppliercode IS NOT NULL
>   AND length(trim(suppliercode)) = 0
> )
> , ranked_on_code AS (
>   SELECT supplierid
>   , trim(suppliercode)||'-'||supplierid AS new_code
>   , rank() OVER (PARTITION BY upper(trim(suppliercode)) ORDER BY supplierid)
>   FROM suppliers
>   WHERE suppliercode IS NOT NULL
>   AND NOT inactive AND type != 'car'
> )
> UPDATE suppliers
> SET suppliercode = new_code
> FROM ranked_on_code
> WHERE suppliers.supplierid = ranked_on_code.supplierid
> AND rank > 1;

I see 2 problems with this query:
1) CTE is just a named subquery, in your query I see no reference to
the “upd_code” CTE.  Therefore it is never gets called;
2) In order to get data-modifying CTE to return anything, you should
use RETURNING clause,   simplest form would be just RETURNING *

Hope this helps.

--
Victor Y. Yegorov