UPDATE并返回一些行两次



我尝试更新并返回行。问题是,我使用带有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

最新更新