2013/1/29 Kong Man <kong_mansatian...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
