Writeable CTE Not Working?

Поиск
Список
Период
Сортировка
От Kong Man
Тема Writeable CTE Not Working?
Дата
Msg-id DUB116-W6555FD44F7B4D966C07B48B1F0@phx.gbl
обсуждение исходный текст
Ответы Re: Writeable CTE Not Working?  (Виктор Егоров <vyegorov@gmail.com>)
Data Loss from SQL SELECT (vs. COPY/pg_dump)  (Kong Man <kong_mansatiansin@hotmail.com>)
Список pgsql-sql
<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> 

В списке pgsql-sql по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: How to access multicolumn function results?
Следующее
От: Виктор Егоров
Дата:
Сообщение: Re: Writeable CTE Not Working?