我尝试更新并返回行。问题是,我使用带有UNION
的嵌套select来获取一些行两次,并且我想让它们返回两次。示例:
表:
First_name | last_name | ready
-----------+-----------+------
john | doe | false
| smith | false
jane | | false
查询:
With list(name) as (
Select First_name
from table1
where First_name Not null and ready=false
union
Select last_name
from table1
where last_name Not null and ready=false
)
Select * from list
返回:
John
jane
doe
smith
现在我想更新select
找到的行,并使用update ... returning
。但是update
只返回三个受影响的行,而我希望它像示例中的select
那样返回这些行。有办法吗?
重写为:
WITH cte AS (
UPDATE table1
SET ready = true
WHERE (first_name IS NOT NULL OR last_name IS NOT NULL)
AND NOT ready
RETURNING first_name, last_name
)
SELECT first_name FROM cte WHERE first_name IS NOT NULL
UNION ALL
SELECT last_name FROM cte WHERE last_name IS NOT NULL;
同样的结果,只是更短更快:这个查询访问table1
一次,而不是像原来那样访问三次
(在测试台上用EXPLAIN ANALYZE
验证卓越性能。)
UNION ALL
就像@Clodoaldo已经提到的。UNION
将消除重复,这要慢得多(这里可能是错误的)。
with list(name) as (
select first_name
from table1
where first_name is not null and ready=false
union all
select last_name
from table1
where last_name is not null and ready=false
), u as (
update table1
set ready = true
where
(first_name is not null or last_name is not null)
and
not ready
)
select * from list
您需要union all
才能拥有这四行。它是is [not] null