使用子查詢更新PostgreSQL 中的表行
Postgres 提供了使用從子查詢派生的值更新現有表行的功能。這對於需要根據資料庫中儲存的另一組資料修改資料的場景非常有用。
問題陳述：
您有一個名為的表“虛擬”，具有代表地址詳細資訊以及客戶、供應商和合作夥伴標誌的各個欄位。目標是根據「cust_original」、「suppl_original」和「partner_original」表中是否存在匹配數據，分別將客戶、供應商和合作夥伴標誌設為 True 或 False，從而更新表。
解決方案：
要使用 SQL 更新語句完成此操作，您可以利用PostgreSQL 的子查詢功能。更新語句採用以下語法：
UPDATE table_name
SET field1 = subquery_result1,
field2 = subquery_result2,
...
FROM (select field1, field2, ...
from subquery) AS subquery_name
WHERE table_name.id = subquery_name.id;在您的特定問題的上下文中，更新查詢將如下所示：
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 dummy 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 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) AS subquery
WHERE dummy.address_id = subquery.address_id;此基於子查詢的更新語句有效更新通過根據子查詢中的匹配資料設定客戶、供應商和合作夥伴標誌來建立「虛擬」表，子查詢執行必要的連結和案例評估。
以上是如何使用子查詢進行條件標誌設定來更新 PostgreSQL 表行？的詳細內容。更多資訊請關注PHP中文網其他相關文章！
