PostgreSQL 连表UPDATE

最新推荐文章于 2025-10-31 08:38:04 发布

原创

最新推荐文章于 2025-10-31 08:38:04 发布 · 3.5k 阅读

标签

#PostgreSQL 连表更新

收录于

PostgreSQL 手册上的update语法如下：

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

下面是根据A表更新B表的例子：

--创建表A：t_1
create table t_1
(
id int,
t_id varchar(200),
name varchar(20)
);
 
--插入数据到表A：t_1
insert into t_1 values (1,'1','a');
insert into t_1 values (2,'2','a');
insert into t_1 values (3,'3','a');
insert into t_1 values (4,'4','a');
insert into t_1 values (5,'5','b');
insert into t_1 values (6,'6','b');
insert into t_1 values (7,'7','a');
 
--创建表B：t_map
create table t_map
(
old_id varchar(200),
new_id varchar(200)
);
 
--插入数据到表B：t_map
insert into t_map values('