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