CODEWITHSUNDEEP
databasepostgresqldatabase-migrationcommon-table-expression
taranaki
taranaki

Reputation: 808

INSERT with several UPDATE statements

What is the best way to solve the following issue with Postgresql?

For each row I INSERT into an article Table based on a SELECT from another table I want to UPDATE certain columns of the inserted articles.

This is my current solution:


-- temporarily alter table to avoid not null issues
ALTER TABLE article ALTER COLUMN fk_article_unit DROP NOT NULL;
(...)

--create article and return inserted pks, store these in a temporary table so they can be used for all following updates
WITH articles AS (
  insert into article
  (
    ...
  )
  select
    ...
  from other_table
  where some_condition
  RETURNING pk
)
SELECT pk INTO temporary temp_articles
FROM articles;

-- update various fk for all newly created articles
UPDATE article
SET fk_article_type =
  (SELECT pk
  FROM article_type
  WHERE unique_id = 'service')
WHERE pk in (select pk from temp_articles);

UPDATE article
SET fk_article_type =
  (SELECT min(pk)
  FROM vat_code)
WHERE fk_article_type is null;

(... several more updates)

--readd no null constraint
ALTER TABLE article ALTER COLUMN fk_article_type SET NOT NULL;
(...)

Upvotes: 1

Views: 47

Answers (1)

Nick
Nick

Reputation: 123

I don't see why this can't be done with a single insert query. If the solution below doesn't apply please provide some additional information on your data model.

insert into article
(
  ...,
  fk_article_type
)
select
  ...,
  coalesce -- if first query is null, then the result of second will be used
  (
      ( -- 1st query
          select pk from article_type where unique_id= 'service'
      ),
      ( -- 2nd query
          select min(pk) from vat_code
      )
  )
from other_table
where some_condition
returning pk;

Upvotes: 1

Related Questions