如何使用子查询进行条件标志设置来更新 PostgreSQL 表行？

使用子查询更新 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中文网其他相关文章！