如何使用子查詢中的值更新 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中文網其他相關文章！