<div dir="ltr"> Can someone explain how this writable CTE works? Or does it not?<br /><br />What I tried to do was to
makethose 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
valueswith '-'||suppliercode.<br /><br />WITH upd_code AS (<br /> UPDATE suppliers SET suppliercode = NULL <br />
WHEREsuppliercode IS NOT NULL <br /> AND length(trim(suppliercode)) = 0<br />)<br />, ranked_on_code AS (<br />
SELECTsupplierid<br /> , trim(suppliercode)||'-'||supplierid AS new_code<br /> , rank() OVER (PARTITION BY
upper(trim(suppliercode))ORDER BY supplierid)<br /> FROM suppliers<br /> WHERE suppliercode IS NOT NULL<br /> AND
NOTinactive AND type != 'car'<br />)<br />UPDATE suppliers<br />SET suppliercode = new_code<br />FROM ranked_on_code<br
/>WHEREsuppliers.supplierid = ranked_on_code.supplierid<br />AND rank > 1;<br /><br />I have seen similar behavior
inthe past and could not explain it. Any explanation is much appreciated.<br />Thanks,<br />-Kong<br /></div>