postgresql 使用子查询更新postgres中的表行

pkwftd7m  于 2023-01-13  发布在  PostgreSQL
关注(0)|答案(7)|浏览(264)

我在postgres 8.4数据库中有这个表:

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
);

我想更新表。最初我使用insert语句测试了我的查询:

insert into address customer,supplier,partner
SELECT  
    case when cust.addr1 is not null then TRUE else FALSE end customer, 
    case when suppl.addr1 is not null then TRUE else FALSE end supplier,
    case when partn.addr1 is not null then TRUE else FALSE end partner
from (
    SELECT *
        from address) pa
    left outer join cust_original 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 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 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

如何将其转换为update语句,即使用select语句返回的值更新现有行?

kkih6yb8

kkih6yb81#

Postgres允许:

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,但对于这种类型的查询,它比标准SQL方便得多。我相信Oracle(至少)接受类似的东西。

zpjtge22

zpjtge222#

您正在使用UPDATE FROM语法。

UPDATE 
  table T1  
SET 
  column1 = T2.column1 
FROM 
  table T2 
  INNER JOIN table T3 USING (column2) 
WHERE 
  T1.column2 = T2.column2;
gv8xihay

gv8xihay3#

如果使用连接没有提高性能,那么我更喜欢通用表表达式(Common Table Expressions,CTE)的可读性:

WITH subquery AS (
    SELECT address_id, customer, address, partn
    FROM  /* big hairy SQL */ ...
)
UPDATE dummy
SET customer = subquery.customer,
    address  = subquery.address,
    partn    = subquery.partn
FROM subquery
WHERE dummy.address_id = subquery.address_id;

恕我直言,更现代一点。

8cdiaqws

8cdiaqws4#

有许多方法可以更新行。
当使用子查询对行进行UPDATE时,可以使用以下任何一种方法。
1.方法-1 [使用直接表引用]

UPDATE
  <table1>
SET
  customer=<table2>.customer,
  address=<table2>.address,
  partn=<table2>.partn
FROM
  <table2>
WHERE
  <table1>.address_id=<table2>.address_i;

说明:table1是要更新的表,table2是从中获取要替换/更新的值的表。我们使用FROM子句来获取table2的数据。WHERE子句将帮助设置正确的数据Map。
1.方法-2 [使用子查询]

UPDATE
  <table1>
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;

说明:这里我们在FROM子句中使用subquerie,并给它一个别名,这样它就像表一样工作。
1.方法-3 [使用多个联接表]

UPDATE
  <table1>
SET
  customer=<table2>.customer,
  address=<table2>.address,
  partn=<table2>.partn
FROM
  <table2> as t2
  JOIN <table3> as t3
  ON
    t2.id = t3.id
WHERE
  <table1>.address_id=<table2>.address_i;

说明:有时候我们会遇到这样的情况,表连接对于获取正确的数据是非常重要的,为了做到这一点,Postgres允许我们在FROM子句中连接多个表。
1.方法-4 [使用WITH语句]

  • 4.1 [使用简单查询]
WITH subquery AS (
    SELECT
      address_id,
      customer,
      address,
      partn
    FROM
      <table1>;
)
UPDATE <table-X>
SET customer = subquery.customer,
    address  = subquery.address,
    partn    = subquery.partn
FROM subquery
WHERE <table-X>.address_id = subquery.address_id;
  • 4.2 [使用具有复杂JOIN的查询]
WITH subquery AS (
    SELECT address_id, customer, address, partn
    FROM
      <table1> as t1
    JOIN
      <table2> as t2
    ON
      t1.id = t2.id;
    -- You can build as COMPLEX as this query as per your need.
)
UPDATE <table-X>
SET customer = subquery.customer,
    address  = subquery.address,
    partn    = subquery.partn
FROM subquery
WHERE <table-X>.address_id = subquery.address_id;

说明:从Postgres 9.1开始,我们引入了(WITH)的概念。使用这个概念,我们可以进行任何复杂的查询并生成所需的结果。这里我们使用这种方法来更新表。
我希望,这会有帮助。😊

pgky5nke

pgky5nke5#

@Mayur***“4.2 [使用带有复杂JOIN的查询]"***使用***通用表表达式(CTE)***对我来说很有用。

WITH cte AS (
SELECT e.id, e.postcode
FROM employees e
LEFT JOIN locations lc ON lc.postcode=cte.postcode
WHERE e.id=1
)
UPDATE employee_location SET lat=lc.lat, longitude=lc.longi
FROM cte
WHERE employee_location.id=cte.id;

希望这对你有帮助...:D

0lvr5msh

0lvr5msh6#

update json_source_tabcol as d
set isnullable = a.is_Nullable
from information_schema.columns as a 
where a.table_name =d.table_name 
and a.table_schema = d.table_schema 
and a.column_name = d.column_name;
trnvg8h3

trnvg8h37#

对于PostgreSQL,请检查https://www.postgresql.org/docs/current/sql-update.html

UPDATE tableA SET (addr1, adrr2) =
    (SELECT addr1, addr2 FROM tableB
     WHERE tableA.id = tableB.tableA_id);

相关问题