Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37072=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1U010K-0006YF-JO
	for pgsql-sql@arkaria.postgresql.org; Tue, 29 Jan 2013 02:32:56 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37072=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1U010K-0003kd-2u
	for pgsql-sql@arkaria.postgresql.org; Tue, 29 Jan 2013 02:32:56 +0000
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <kong_mansatiansin@hotmail.com>) id 1U010I-0003jd-OG
	for pgsql-sql@postgresql.org; Tue, 29 Jan 2013 02:32:54 +0000
Received: from dub0-omc2-s11.dub0.hotmail.com ([157.55.1.150])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <kong_mansatiansin@hotmail.com>) id 1U010H-0007dQ-7Q
	for pgsql-sql@postgresql.org; Tue, 29 Jan 2013 02:32:54 +0000
Received: from DUB116-W6 ([157.55.1.137]) by dub0-omc2-s11.dub0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.4675);
	Mon, 28 Jan 2013 18:32:52 -0800
X-EIP: [8v2TNuswp8dCtfBxm4Dek1PlZrT6fg59]
X-Originating-Email: [kong_mansatiansin@hotmail.com]
Message-ID: <DUB116-W6555FD44F7B4D966C07B48B1F0@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_8023ae8b-619c-4252-aea9-718d1d03c10b_"
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
Importance: Normal
MIME-Version: 1.0
X-OriginalArrivalTime: 29 Jan 2013 02:32:52.0018 (UTC)
	FILETIME=[EFAFE120:01CDFDC8]
X-Pg-Spam-Score: 0.3 (/)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

--_8023ae8b-619c-4252-aea9-718d1d03c10b_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


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=2C but non-null=2C supplie=
rcode=2C then (2) appending the supplierid values to the suppliercode value=
s for those duplicates.  The writeable CTE=2C upd_code=2C did not appear to=
 work=2C allowing the final UPDATE statement to=2C unexpectedly=2C fill wha=
t used to be empty values with '-'||suppliercode.

WITH upd_code AS (
  UPDATE suppliers SET suppliercode =3D NULL=20
  WHERE suppliercode IS NOT NULL=20
  AND length(trim(suppliercode)) =3D 0
)
=2C ranked_on_code AS (
  SELECT supplierid
  =2C trim(suppliercode)||'-'||supplierid AS new_code
  =2C rank() OVER (PARTITION BY upper(trim(suppliercode)) ORDER BY supplier=
id)
  FROM suppliers
  WHERE suppliercode IS NOT NULL
  AND NOT inactive AND type !=3D 'car'
)
UPDATE suppliers
SET suppliercode =3D new_code
FROM ranked_on_code
WHERE suppliers.supplierid =3D ranked_on_code.supplierid
AND rank > 1=3B

I have seen similar behavior in the past and could not explain it.  Any exp=
lanation is much appreciated.
Thanks=2C
-Kong
 		 	   		  =

--_8023ae8b-619c-4252-aea9-718d1d03c10b_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style><!--
.hmmessage P
{
margin:0px=3B
padding:0px
}
body.hmmessage
{
font-size: 10pt=3B
font-family:Tahoma
}
--></style></head>
<body class=3D'hmmessage'><div dir=3D'ltr'>
Can someone explain how this writable CTE works?&nbsp=3B Or does it not?<br=
><br>What I tried to do was to make those non-null/non-empty values of supp=
liers.suppliercode unique by (1) nullifying any blank=2C but non-null=2C su=
ppliercode=2C then (2) appending the supplierid values to the suppliercode =
values for those duplicates.&nbsp=3B The writeable CTE=2C upd_code=2C did n=
ot appear to work=2C allowing the final UPDATE statement to=2C unexpectedly=
=2C fill what used to be empty values with '-'||suppliercode.<br><br>WITH u=
pd_code AS (<br>&nbsp=3B UPDATE suppliers SET suppliercode =3D NULL <br>&nb=
sp=3B WHERE suppliercode IS NOT NULL <br>&nbsp=3B AND length(trim(supplierc=
ode)) =3D 0<br>)<br>=2C ranked_on_code AS (<br>&nbsp=3B SELECT supplierid<b=
r>&nbsp=3B =2C trim(suppliercode)||'-'||supplierid AS new_code<br>&nbsp=3B =
=2C rank() OVER (PARTITION BY upper(trim(suppliercode)) ORDER BY supplierid=
)<br>&nbsp=3B FROM suppliers<br>&nbsp=3B WHERE suppliercode IS NOT NULL<br>=
&nbsp=3B AND NOT inactive AND type !=3D 'car'<br>)<br>UPDATE suppliers<br>S=
ET suppliercode =3D new_code<br>FROM ranked_on_code<br>WHERE suppliers.supp=
lierid =3D ranked_on_code.supplierid<br>AND rank &gt=3B 1=3B<br><br>I have =
seen similar behavior in the past and could not explain it.&nbsp=3B Any exp=
lanation is much appreciated.<br>Thanks=2C<br>-Kong<br> 		 	   		  </div></=
body>
</html>=

--_8023ae8b-619c-4252-aea9-718d1d03c10b_--