使用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
是新手我没有转换为更新语句即,用select语句返回的值更新现有行 . 任何帮助都非常感谢 .
4 回答
Postgres允许:
此语法不是标准SQL,但对于此类查询比标准SQL更方便 . 我相信Oracle(至少)接受类似的东西 .
你是在
UPDATE FROM
语法之后 .References
此处代码示例:GROUP BY in UPDATE FROM clause
And here
Formal Syntax Specification
如果使用连接没有性能提升,那么我更喜欢通用表表达式(CTE)以提高可读性:
恕我直言更现代 .