Re: Writeable CTE Not Working?

2013/1/29 Kong Man <kong_mansatiansin(at)hotmail(dot)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