mySql,Pgsql,kingbase连表更新

原文链接: https://www.jianshu.com/p/0df5797f7b73
Table a:
    id   | 主键 
    code | 编码

Table b:
    id   | 主键 
    aId  | 关联a表id
    code | 编码
    name | 名称

例，a、b表一对一，更新a表的code为b表code：

MySQL:

UPDATE a JOIN b ON a.id = b.aId SET a.code=b.code;

通用语法：
UPDATE table1 inner/left/right join table2/(select columns from table3  
        [inner/left/right join on condition]  [where conditions]) as t3
ON condition
SET column1 = value1,column2 = value2,...
[WHERE conditions]; 

PgSQL:

方法1.

UPDATE a SET code=b.code FROM b where a.id = b.aId;

方法2.较为灵活

UPDATE  a
SET     code = b.code
FROM    (
        SELECT  a.id, b.aId, b.code
        FROM    a
                INNER JOIN b  ON a.id = b.aId
        ) AS tb
WHERE   a.id = tb.id

更多例子：

Table main:
    id   | 主键 
    code | 编码

Table detail:
    id   | 主键 
    aId  | 关联main表id

detail表详情数据通过aId关联main表，将detail表的aId修改为对应的main表中以A开头的code中的数字

UPDATE detail 
SET aId = cast( SUBSTRING( tbs.code, E'([0-9]+)' ) AS INT8 ) 
FROM
    (
        SELECT
            b.id,b.aId, a.code
        FROM
            detail b 
            INNER join main a on a.id = b.aId
        WHERE
            a.code LIKE 'A%'  
    ) as tbs
where detail.id =tbs.id