PostgreSQL | 数据库语法-两表关联更新语法

PostgreSQL 两表关联更新

问题：我需要对目标表cq_compliance_question的serial_number字段的值进行维护，由于cq_compliance_question数据量太大，所以我创建了临时表cq_compliance_question_temp来辅助操作。所以，我需要通过这两张表关联来完成对目标表字段serial_number值的维护。

我采用MySQL的写法：

UPDATE cq_compliance_question cq,
 cq_compliance_question_temp cqt
SET cq.serial_number = cqt.serial_number_2
WHERE
    cq."id" = cqt."id"

但是此时Navicat是报错的，错误信息：

[SQL]UPDATE cq_compliance_question cq,
 cq_compliance_question_temp cqt
SET cq.serial_number = cqt.serial_number_2
WHERE
    cq."id" = cqt."id"

[Err] ERROR:  syntax error at or near ","
LINE 1: UPDATE cq_compliance_question cq,
                                        ^

说我语法错误，但这种写法在MySQL是ok的。

所以我开始对该写法进行修改。
第一次修改：

UPDATE cq_compliance_question cq
SET cq.serial_number = cqt.serial_number_2
FROM
    cq_compliance_question_temp cqt
WHERE
    cq."id" = cqt."id"

报错，错误信息：

[SQL]UPDATE cq_compliance_question cq
SET cq.serial_number = cqt.serial_number_2
FROM
    cq_compliance_question_temp cqt
WHERE
    cq."id" = cqt."id"

[Err] ERROR:  column "cq" of relation "cq_compliance_question" does not exist
LINE 2: SET cq.serial_number = cqt.serial_number_2
            ^

说在表"cq_compliance_question"中不存在column "cq"。所以我想是表的别名出问题了，去掉表别名试试。

第二次修改：

UPDATE cq_compliance_question
SET serial_number = cqt.serial_number_2
FROM
    cq_compliance_question_temp cqt
WHERE
    "id" = cqt."id"

还是报错，错误信息：

[SQL]UPDATE cq_compliance_question
SET serial_number = cqt.serial_number_2
FROM
    cq_compliance_question_temp cqt
WHERE
    "id" = cqt."id"

[Err] ERROR:  column reference "id" is ambiguous
LINE 6:  "id" = cqt."id"
         ^

说id是不明确的。这就是说无法确定id是属于哪张表的，那么我对id指明表，再试试。

第三次修改：

UPDATE cq_compliance_question 
SET serial_number = cqt.serial_number_2
FROM
    cq_compliance_question_temp cqt
WHERE
    cq_compliance_question."id" = cqt."id"

此时执行就ok：

[SQL]UPDATE cq_compliance_question 
SET serial_number = cqt.serial_number_2
FROM
    cq_compliance_question_temp cqt
WHERE
    cq_compliance_question."id" = cqt."id"

时间: 0.017s

受影响的行: 50

我再试试加上表别名，第四次修改：

UPDATE cq_compliance_question cq
SET serial_number = cqt.serial_number_2
FROM
    cq_compliance_question_temp cqt
WHERE
    cq."id" = cqt."id"

此时执行，也是ok的：

[SQL]UPDATE cq_compliance_question cq
SET serial_number = cqt.serial_number_2
FROM
    cq_compliance_question_temp cqt
WHERE
    cq."id" = cqt."id"

时间: 0.037s

受影响的行: 50

问题解决。
可以验证出PostgreSQL在两表关联更新时，跟MySQL是不一样的。关键词set后面的目标列名是不能用表名或者表别名来引用的，直接使用列名即可，否则会出现语法错误。

当然，我还没有验证set多个列和where后有多个联合查询条件的情况。如果出现问题，也可以根据这个思路修改来验证。

编者按：本文由弄青春原创，如果您喜欢，劳驾您点个赞，也欢迎您留下宝贵的评论！若要转载，请注明出处！