1

I'm using PostgreSQL.

I have tables A, B, C & D.

Table A has a foreign key to table B (so one-to-many).

Tables C & D have foreign keys to table A (one-to-many). So it looks like:

B :-> A :-> C & D

Now i want to add a intermediate table E to make relations:

E :-> C & D (one-to-many)

B -> E (one-to-one with fk)

B :-> A (one-to-many)

I need it to introduce table F which will also has a foreign key to table E.

Table E has just a one identity column 'id'.

Now i have to write a migration and have no idea how to add rows to E and update B simultaneously

In pseudo-SQL it must be look like:

UPDATE B SET A_id = id INSERT INTO A (id)

l1pton17
  • 444
  • 4
  • 16
  • I'm not clear on your question. Do you just need a transaction? `BEGIN; INSERT...; UPDATE ...; COMMIT;` – hunteke Oct 27 '17 at 02:41

2 Answers2

1

You can use a WITH statement for that:

WITH inserted_rows AS (
    INSERT INTO A(...) VALUES (...) RETURNING *
) UPDATE B SET A_id = a.id FROM inserted_rows a WHERE ...;

The WHERE clause should filter the matching rows of B. Unfortunately, your question is very broad so I can't give you a more detailed example.

clemens
  • 16,716
  • 11
  • 50
  • 65
-1

Adding a temporary column helps me.

CREATE TABLE promo_codes_definitions (
    id              serial      PRIMARY KEY
   ,emission_id     integer     REFERENCES promo_codes_emissions(id)
   ,when_created    timestamp   NOT NULL DEFAULT TIMEZONE('UTC', NOW())
);

INSERT INTO promo_codes_definitions(emission_id, when_created)
SELECT pce.id, pce.when_created
FROM promo_codes_emissions pce;

UPDATE promo_codes_emissions pce
SET promo_codes_definition_id = pcd.id
FROM promo_codes_definitions pcd
WHERE pce.id = pcd.emission_id;

ALTER TABLE promo_codes_definitions
    DROP COLUMN emission_id;
l1pton17
  • 444
  • 4
  • 16