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