Update Returning as subquery
От | pascal+postgres@ensieve.org |
---|---|
Тема | Update Returning as subquery |
Дата | |
Msg-id | F1134784-45F6-48C9-BA79-98F9BBE00051@ensieve.org обсуждение исходный текст |
Ответы |
Re: Update Returning as subquery
Re: Update Returning as subquery |
Список | pgsql-sql |
Hi, I want to update some values in a table, and need to count the number of values actually changed; but ROW_COUNT returns thenumber of total rows touched. But this gives a syntax error: SELECT count(*) INTO my_count FROM ( UPDATE stuff SET value = maybe_null(key) --^ WHERE value IS NULL RETURNING value ) AS t WHERE value IS NOT NULL; Why is that forbidden? Isn't the purpose of a RETURNING clause to return values like a SELECT statement would, and shouldn'tit therefore be allowed to occur in the same places? I switched it around using a CTE in this case: WITH new_values AS ( SELECT key, maybe_null(key) AS value FROM stuff WHERE value IS NULL) UPDATE stuff AS s SET value = n.value FROM new_values AS n WHERE n.key = s.key AND n.value IS NOT NULL; Which only touches rows that will be changed and returns a useful ROW_COUNT, but needs a join. Cheers, -- Pascal Germroth
В списке pgsql-sql по дате отправления: