如何使用子查询更新 PostgreSQL 表行？

使用子查询更新 PostgreSQL 中的表行

在 PostgreSQL 中，可以通过便捷的语法使用 SELECT 语句返回的值更新现有行。

考虑提供的表模式：

CREATE TABLE public.dummy (
  address_id SERIAL,
  addr1 character(40),
  addr2 character(40),
  city character(25),
  state character(2),
  zip character(5),
  customer boolean,
  supplier boolean,
  partner boolean
);

根据子查询，使用以下语法：

UPDATE dummy
SET customer = subquery.customer,
    address = subquery.address,
    partn = subquery.partn
FROM (
  SELECT address_id, customer, address, partn
  FROM /* big hairy SQL */ ...
) AS subquery
WHERE dummy.address_id = subquery.address_id;

此语法不是标准 SQL，但对于此类查询很方便。例如，要根据复杂联接的结果更新 customer、address 和partn 列，您可以使用以下子查询：

SELECT address_id,
       CASE
           WHEN cust.addr1 IS NOT NULL THEN TRUE
           ELSE FALSE
       END AS customer,
       CASE
           WHEN suppl.addr1 IS NOT NULL THEN TRUE
           ELSE FALSE
       END AS address,
       CASE
           WHEN partn.addr1 IS NOT NULL THEN TRUE
           ELSE FALSE
       END AS partn
FROM (
    SELECT *
    FROM address
) pa
LEFT OUTER JOIN cust_original AS cust
    ON (pa.addr1 = cust.addr1
        AND pa.addr2 = cust.addr2
        AND pa.city = cust.city
        AND pa.state = cust.state
        AND SUBSTRING(cust.zip, 1, 5) = pa.zip
    )
LEFT OUTER JOIN supp_original AS suppl
    ON (pa.addr1 = suppl.addr1
        AND pa.addr2 = suppl.addr2
        AND pa.city = suppl.city
        AND pa.state = suppl.state
        AND pa.zip = SUBSTRING(suppl.zip, 1, 5)
    )
LEFT OUTER JOIN partner_original AS partn
    ON (pa.addr1 = partn.addr1
        AND pa.addr2 = partn.addr2
        AND pa.city = partn.city
        AND pa.state = partn.state
        AND pa.zip = SUBSTRING(partn.zip, 1, 5)
)
WHERE pa.address_id = address_id;

通过执行此更新，虚拟表中的指定列将使用从子查询获得的值进行更新。

以上是如何使用子查询更新 PostgreSQL 表行？的详细内容。更多信息请关注PHP中文网其他相关文章！