[SQL] Writeable CTE Not Working?

[Kong Man]

Can someone explain how this writable CTE works?  Or does it not?

What I tried to do was to make those non-null/non-empty values of 
suppliers.suppliercode unique by (1) nullifying any blank, but non-null, 
suppliercode, then (2) appending the supplierid values to the suppliercode 
values for those duplicates.  The writeable CTE, upd_code, did not appear to 
work, allowing the final UPDATE statement to, unexpectedly, fill what used to 
be empty values with '-'||suppliercode.

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 have seen similar behavior in the past and could not explain it.  Any 
explanation is much appreciated.
Thanks,
-Kong
  

Re: [SQL] Writeable CTE Not Working?

[Виктор Егоров]

2013/1/29 Kong Man :
> 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