使用subquery更新postgres中的表行

使用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 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

是新手，我正在失败转换到更新语句ie。，用select语句返回的值更新现有行。

557452 次浏览
小开

你在寻找UPDATE FROM语法。

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

参考文献

小开
最佳答案

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要方便得多。我相信甲骨文(至少)接受类似的东西。

小开

如果使用连接没有性能提升，那么我更喜欢通用表表达式(CTEs)的可读性:

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;

以我之见，更现代一点。

小开 
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;
小开

有许多方法可以更新行。

当涉及到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 子句将帮助设置正确的数据映射
  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子句中使用子查询， 给它一个别名。这样它就像表。

  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)概念已经被定义为 介绍了。使用它，我们可以进行任何复杂的查询和生成 想要的结果。这里我们使用这种方法来更新表

我希望，这将是有用的..😊

小开

@Mayur "4.2[使用复杂连接查询]" .和 CTEs (Common Table Expressions) . /em> . /em> . /em> . /em>对我有用。

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

小开

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

提交答案