PostgreSQL 连表UPDATE

淩亂°似流年 2022-11-28 13:19 151阅读 0赞

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('1','001');
    insert into t_map values('2','002');
    insert into t_map values('3','003');
    insert into t_map values('4','004');
    insert into t_map values('5','005');
    insert into t_map values('6','006');

select * from t_1;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvbmRh_size_16_color_FFFFFF_t_70][]

select * from t_map;

![20200817173029323.png][]

--根据表t_map的old_id更新表t_1的t_id为表t_map的new_id
    update t_1 t
    set t_id = map.new_id
    from t_map map
    where t.t_id = map.old_id
    and t.name = 'a'

更新后如下：

select * from t_1 order by id;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvbmRh_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvbmRh_size_16_color_FFFFFF_t_70]: /images/20221124/7386594b5a9547b7bab9bce45f299fe0.png
[20200817173029323.png]: /images/20221124/35f3ac518e7c4fff8a17208c1e51ad3a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvbmRh_size_16_color_FFFFFF_t_70 1]: /images/20221124/58d3a55042c944309557817c9578e817.png