[SQL] 연루되는 테이블에서 선택하는 단일 쿼리에서 여러 테이블에 INSERT 행

연루되는 테이블에서 선택하는 단일 쿼리에서 여러 테이블에 INSERT 행

나는 다음과 같은 형태 (즉, 모든 foo는 정확히 하나의 줄에 링크)의 두 개의 테이블이있다.

CREATE TABLE foo (
    id INTEGER PRIMARY KEY,
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,
    ...,
    bar_id INTEGER UNIQUE NOT NULL,
    FOREIGN key (bar_id) REFERENCES bar(id)
);

CREATE TABLE bar (
    id INTEGER PRIMARY KEY,
    z INTEGER NOT NULL,
    ...
);

이 중첩 된 쿼리를 사용하여 특정 조건을 만족 foo는 행을 복사하는 것은 쉽다 :

INSERT INTO foo (...) (SELECT ... FROM foo WHERE ...)

하지만 foo는 각 행에 대해 표시 줄에있는 관련 행의 복사본을 만들고 새로운 foo는 행에 바의 ID를 삽입하는 방법을 알아낼 수 없습니다. 단일 쿼리에이 일을 어떤 방법이 있나요?

원하는 결과의 구체적인 예 :

-- Before query:

foo(id=1,x=3,y=4,bar_id=100)  .....  bar(id=100,z=7)
foo(id=2,x=9,y=6,bar_id=101)  .....  bar(id=101,z=16)
foo(id=3,x=18,y=0,bar_id=102) .....  bar(id=102,z=21)


-- Query copies all pairs of foo/bar rows for which x>3:

-- Originals
foo(id=1,x=3,y=4,bar_id=101)  .....  bar(id=101,z=7)
foo(id=2,x=9,y=6,bar_id=102)  .....  bar(id=102,z=16)
foo(id=3,x=18,y=0,bar_id=103) .....  bar(id=103,z=21)

-- "Copies" of foo(id=2,...) and foo(id=3,...), with matching copies of
-- bar(id=102,...) and bar(id=103,...)
foo(id=4,x=9,y=6,bar_id=104)  .....  bar(id=104,z=16)
foo(id=5,x=18,y=0,bar_id=105) .....  bar(id=105,z=21)

해결법

1. ==============================

   1.... OP에서 몇 가지 추가 정보를 원하시면 후. 이 데모를 고려하십시오

   ... OP에서 몇 가지 추가 정보를 원하시면 후. 이 데모를 고려하십시오

   -- DROP TABLE foo; DROP TABLE bar;
   
   CREATE TEMP TABLE bar (
    id serial PRIMARY KEY  -- using a serial column!
   ,z  integer NOT NULL
   );
   
   CREATE TEMP TABLE foo (
    id     serial PRIMARY KEY  -- using a serial column!
   ,x      integer NOT NULL
   ,y      integer NOT NULL
   ,bar_id integer UNIQUE NOT NULL REFERENCES bar(id)
   );
   

   값 삽입 - 첫째 바. 이 같은 질문에 테스트 데이터를 제공하는 경우는 매우 도움이 될 것입니다!

   INSERT INTO bar (id,z) VALUES
    (100, 7)
   ,(101,16)
   ,(102,21);
   
   INSERT INTO foo (id, x, y, bar_id) VALUES
    (1, 3,4,100)
   ,(2, 9,6,101)
   ,(3,18,0,102);
   

   현재 값 시퀀스를 설정하거나 우리는 중복 키 위반을 얻을 :

   SELECT setval('foo_id_seq', 3);
   SELECT setval('bar_id_seq', 102);
   

   체크 무늬:

   -- SELECT nextval('foo_id_seq')
   -- SELECT nextval('bar_id_seq')
   -- SELECT * from bar;
   -- SELECT * from foo;
   

   질문:

   WITH a AS (
       SELECT f.x, f.y, bar_id, b.z
       FROM   foo f
       JOIN   bar b ON b.id = f.bar_id
       WHERE  x > 3
       ),b AS (
       INSERT INTO bar (z)
       SELECT z
       FROM   a
       RETURNING z, id AS bar_id
       )
   INSERT INTO foo (x, y, bar_id)
   SELECT a.x, a.y, b.bar_id
   FROM   a
   JOIN   b USING (z);
   

   이것은 당신의 마지막 업데이트가 무엇을 설명해야한다.

   쿼리는 z는 UNIQUE 있다고 가정합니다. z는 고유하지 않으면, 그것은 더 복잡한 가져옵니다. 이 경우 윈도우 기능 ROW_NUMBER ()를 사용하여 준비 솔루션이 관련 질문에 대해 쿼리 2를 참조하십시오.

   하나의 통일 된 테이블과 foo는 바 사이 1 관계 : 또한, 1 교체를 고려.

   추가 정보를 원하시면 후 두 번째 대답.

   단일 쿼리에서 foo는 바로 행을 추가하려는 경우, 당신은 PostgreSQL의 9.1 이후 CTE를 수정 데이터를 사용할 수 있습니다 :

   WITH x AS (
       INSERT INTO bar (col1, col2)
       SELECT f.col1, f.col2
       FROM   foo f
       WHERE  f.id BETWEEN 12 AND 23 -- some filter
       RETURNING col1, col2, bar_id  -- assuming bar_id is a serial column
       )
   INSERT INTO foo (col1, col2, bar_id)
   SELECT col1, col2, bar_id
   FROM   x;
   

   나는 바에서 그들을 삽입, foo는의 값을 끌어들이 자동 생성 bar_id과 함께 돌아와 foo는에 삽입을합니다. 당신도 다른 데이터를 사용할 수 있습니다.

   여기 sqlfiddle에 함께 플레이 할 수있는 작업 데모입니다.

   해명하기 전에 기본 정보와 원래 대답. 기본 형식은 다음과 같습니다

   INSERT INTO foo (...)
   SELECT ... FROM foo WHERE ...
   

   어떤 괄호는 필요하지 않습니다. 당신은 모든 테이블과 동일한 기능을 수행 할 수 있습니다

   INSERT INTO foo (...)
   SELECT ... FROM bar WHERE ...
   

   그리고 당신은 당신이 선택에 삽입 테이블에 가입 할 수 있습니다 :

   INSERT INTO foo (...)
   SELECT f.col1, f.col2, .. , b.bar_id
   FROM   foo f
   JOIN   bar b USING (foo_id);  -- present in foo and bar
   

   그냥 다른 같은 SELECT의 - 그건 당신이에 삽입하는 테이블을 포함 할 수 있습니다. 행이 먼저 판독하고 삽입된다.

2. ==============================

   2.막대의 ID가 시리얼과 NEXTVAL 디폴트 값 ( 'bar_id_seq':: regclass 형)가있는 경우 수동으로 CTE의 새로운 ID를 얻기 위해이 함수를 호출 할 수 있습니다

   막대의 ID가 시리얼과 NEXTVAL 디폴트 값 ( 'bar_id_seq':: regclass 형)가있는 경우 수동으로 CTE의 새로운 ID를 얻기 위해이 함수를 호출 할 수 있습니다

   with
   s_bar as (
     SELECT id, z, nextval('bar_id_seq'::regclass) new_id
     FROM   bar
     WHERE  ...
   ),
   s_foo as (
     SELECT x, y, bar_id
     FROM   foo
     WHERE  ...
   ),
   i_bar as (
     INSERT INTO bar (id, z)
     SELECT new_id, z
     FROM   s_bar
   ),
   i_foo as (
     INSERT INTO foo (x, y, bar_id)
     SELECT f.x, f.y, b.new_id
     FROM   s_foo f
     JOIN   s_bar b on b.id = f.bar_id
   )
   SELECT 1
   

from https://stackoverflow.com/questions/10471757/insert-rows-into-multiple-tables-in-a-single-query-selecting-from-an-involved-t by cc-by-sa and MIT license