如何使用子查询中的值更新 PostgreSQL 行？

使用子查询更新 PostgreSQL 表行

要使用从子查询返回的值更新 PostgreSQL 表中的现有行，您可以使用以下命令语法：

UPDATE table_name
SET column_name = subquery.column_name
FROM (SELECT ...) AS subquery
WHERE table_name.id = subquery.id;

将示例表视为虚拟表提供：

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
)
WITH (
  OIDS=FALSE
);

要根据 select 语句返回的值更新客户、供应商和合作伙伴列，您可以使用以下语法：

UPDATE dummy
SET customer = subquery.customer,
    supplier = subquery.supplier,
    partner = subquery.partner
FROM (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 supplier,
            CASE WHEN partn.addr1 IS NOT NULL THEN TRUE ELSE FALSE END AS partner
      FROM address AS 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 CAST(cust.zip AS VARCHAR(5)) = CAST(pa.zip AS VARCHAR(5)))
      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 CAST(pa.zip AS VARCHAR(5)) = CAST(CAST(suppl.zip AS VARCHAR(25)) AS VARCHAR(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 CAST(pa.zip AS VARCHAR(5)) = CAST(CAST(partn.zip AS VARCHAR(25)) AS VARCHAR(5)))) AS subquery
WHERE dummy.address_id = subquery.address_id;

此查询执行以下操作：

• 从连接虚拟表的子查询中选择虚拟表中每一行的客户、供应商和合作伙伴值包含 cust_original、supp_original 和 Partner_original 表的虚拟表。
• 将虚拟表中的客户、供应商和合作伙伴列设置为子查询返回的值。
• 仅更新子查询中具有匹配的 address_id 的虚拟表。

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