duanmengsuo9302 2019-05-16 08:50
浏览 707
已采纳

在一个Postgresql中更新多个表

I have three tables A, B, C. Table B & Table C consist foreign key from table A's id.

When updating Form, data will be updated to these three tables by identifying the foreign key.

CREATE TABLE A(
   id serial PRIMARY KEY, 
   id_theme_thesaurus  text,
   id_series text,
   id_geo_form text
);

CREATE  TABLE B(
   id  serial PRIMARY KEY,
   meta_id int,
   cit_title text,
   cit_pub_date text
);

CREATE  TABLE C(
   id serial PRIMARY KEY,
   meta_id int,
   id_originator text
);

And here is my update statement

UPDATE B AS c
SET cit_pub_date = '2016'
FROM t_metadata AS m
WHERE c.meta_id = m.id
AND m.id = '2';

UPDATE C AS o
SET id_originator = 'UN'
FROM t_metadata AS m
WHERE o.meta_id = m.id
AND m.id = '2';

How can I join these two UPDATE statements to become one in the join?

  • 写回答

1条回答 默认 最新

  • douzi0609 2019-05-16 09:16
    关注

    The CTE(with clause) could help to solve your problem, it would implement that update two tables in just one sql. As below:

    with tmp1 as (
    UPDATE B AS c SET cit_pub_date = '2016' FROM t_metadata AS m WHERE c.meta_id = m.id AND m.id = '2'
    )
    ,tmp2 as (
    UPDATE C AS o SET id_originator = 'UN' FROM t_metadata AS m WHERE o.meta_id = m.id AND m.id = '2' returning o.meta_id
    )
    select * from tmp2
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 BDSBAS-B1C和B1C信号有什么不同
  • ¥15 在半圆平面内随机生成点坐标
  • ¥15 系统容量变化的几种多址方式TDMA, CDMA,FDMA,OFDMA 对比,应该给的是一个曲线 图,随着系统容量的增加,几种多址方式性能的对比 图,MATLAB程序仿真折线图
  • ¥15 用visual Studio 写c ++只运行上一个旧代码的运行结果是怎么回事
  • ¥15 系统容量变化的几种多址方式(TDMA,FDMA,OFDMA,CDMA)对比(相关搜索:曲线图)
  • ¥15 worldclim 历史及未来气候数据矫正
  • ¥15 ajax服务器不能下载
  • ¥15 运用c++和opencv实现二维码的识别和三维坐标的建立
  • ¥100 理想汽车的ADB为什么到了国外换了SIM就可以打开?
  • ¥15 k210烧入flash报错error:2005