From: Kong Man <kong_mansatiansin@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Writeable CTE Not Working?
Date: Mon, 28 Jan 2013 18:32:51 -0800
Message-ID: <DUB116-W6555FD44F7B4D966C07B48B1F0@phx.gbl> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kong_mansatiansin@hotmail.com
Subject: Re: Writeable CTE Not Working?
In-Reply-To: <DUB116-W6555FD44F7B4D966C07B48B1F0@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox