CODEWITHSUNDEEP
postgresqlsql-updatesql-insert
ffigari
ffigari

Reputation: 451

postgresql | batch update with insert in single query, 1:n to 1:1

I need to turn a 1:n relationship into a 1:1 relationship with the data remaining the same. I want to know if is it possible to achieve this with a single pure sql (no plpgsql, no external language). Below there are more details, a MWE and some extra context.

To illustrate, if I have

+------+--------+     +------+----------+--------+  
| id   | name   |     | id   | foo_id   | name   |  
|------+--------|     |------+----------+--------|  
| 1    | foo1   |     | 1    | 1        | baz1   |  
| 2    | foo2   |     | 2    | 1        | baz2   |  
| 3    | foo3   |     | 3    | 2        | baz3   |  
+------+--------+     | 4    | 2        | baz4   |  
                      | 5    | 3        | baz5   |
                      +------+----------+--------+

I want to get to

+------+--------+     +------+----------+--------+    
| id   | name   |     | id   | foo_id   | name   |    
|------+--------|     |------+----------+--------|    
| 4    | foo1   |     | 1    | 4        | baz1   |    
| 5    | foo1   |     | 2    | 5        | baz2   |    
| 6    | foo2   |     | 3    | 6        | baz3   |    
| 7    | foo2   |     | 4    | 7        | baz4   |    
| 8    | foo3   |     | 5    | 8        | baz5   |    
+------+--------+     +------+----------+--------+

Here is some code to set up the tables if needed:

drop table if exists baz;
drop table if exists foo;
create table foo(
  id   serial primary key,
  name varchar
);
insert into foo (name) values
  ('foo1'),
  ('foo2'),
  ('foo3');

create table baz(
  id     serial primary key,
  foo_id integer references foo (id),
  name   varchar
);
insert into baz (foo_id, name) values
  (1, 'baz1'),
  (1, 'baz2'),
  (2, 'baz3'),
  (2, 'baz4'),
  (3, 'baz5');

I managed to work out the following query that updates only one entry (ie, the pair <baz id, foo id> has to be provided):

with
existing_foo_values as (
  select name from foo where id = 1
),
new_id as (
  insert into foo(name)
  select name from existing_foo_values
  returning id
)
update baz
set foo_id = (select id from new_id)
where id = 1;

The real case scenario (a db migration in a nodejs environment) was solved using something similar to

const existingPairs = await runQuery(`
  select id, foo_id from baz
`);
await Promise.all(existingPairs.map(({
  id, foo_id
}) => runQuery(`
  with
  existing_foo_values as (
    select name from foo where id = ${foo_id}
  ),
  new_id as (
    insert into foo(name)
    select name from existing_foo_values
    returning id
  )
  update baz
  set foo_id = (select id from new_id)
  where id = ${id};
`)));

// Then delete all the orphan entries from `foo`

Upvotes: 1

Views: 337

Answers (1)

Blue Star
Blue Star

Reputation: 1952

Here's a solution that works by first putting together what we want foo to look like (using values from the sequence), and then making the necessary changes to the two tables based on that.

WITH new_ids AS (
    SELECT nextval('foo_id_seq') as foo_id, baz.id as baz_id, foo.name as foo_name
    FROM foo 
    JOIN baz ON (foo.id = baz.foo_id)
), 
inserts AS (
    INSERT INTO foo (id, name)
    SELECT foo_id, foo_name 
    FROM new_ids
), 
updates AS (
    UPDATE baz 
    SET foo_id = new_ids.foo_id 
    FROM new_ids 
    WHERE new_ids.baz_id = baz.id
) 
DELETE FROM foo 
WHERE id < (SELECT min(foo_id) FROM new_ids);

Upvotes: 1

Related Questions