我想在一个 CTE 中插入主表后,将外键插入到多个表中。我找不到解决方案,所以这很可能是不可能的......
看这个例子:
CREATE TABLE test_main (main_id serial NOT NULL, main_name character varying(64) default null);
CREATE TABLE test_sub_one (sub_one_id serial NOT NULL,sub_one_main_id integer NOT NULL,sub_one_name character varying(64) default null);
CREATE TABLE test_sub_two (sub_two_id serial NOT NULL,sub_two_main_id integer NOT NULL,sub_two_name character varying(64) default null);
WITH main as (
INSERT INTO test_main (main_name) VALUES ('test1') RETURNING main_id
)
INSERT INTO test_sub_one (sub_one_main_id,sub_one_name) SELECT main_id, 'testsub1' FROM main,
INSERT INTO test_sub_two (sub_two_main_id,sub_two_name) SELECT main_id, 'testsub2' FROM main;
对第二个插入使用另一个 CTE:
WITH main as (
INSERT INTO test_main (main_name) VALUES ('test1') RETURNING main_id
), sub1 as (
INSERT INTO test_sub_one (sub_one_main_id,sub_one_name)
SELECT main_id, 'testsub1' FROM main
)
INSERT INTO test_sub_two (sub_two_main_id,sub_two_name)
SELECT main_id, 'testsub2' FROM main;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)