duanmengsuo9302
duanmengsuo9302
2019-05-16 08:50

在一个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 douzi0609 2年前

    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
    
    点赞 评论 复制链接分享

为你推荐