From: | pascal+postgres(at)ensieve(dot)org |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Update Returning as subquery |
Date: | 2014-08-10 16:15:20 |
Message-ID: | F1134784-45F6-48C9-BA79-98F9BBE00051@ensieve.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 the number 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't it 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-08-10 17:17:48 | Re: Update Returning as subquery |
Previous Message | Adrian Klaver | 2014-08-06 13:58:27 | Re: function call |