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
