Postgres Update Multiple Rows with Multiple Returns Preserving Order

So I have a Table A that is missing a relation with Table B such that I want to add the relationship information preserving the order based on insert. Table B can have multiple pieces where UUIDs are unique to a grouping and the order is preserved by and index.

So I can have in Table B:

| dfe63310-6a01-40e1-a44e-b9cc2f4ed37a | 0 |
| dfe63310-6a01-40e1-a44e-b9cc2f4ed37a | 1 |

What I want to do is add those columns to Table A and copy that information over to Table A in that order where we can assume that Table A has entries that match the same count as Table B.

Example Data:

 id |                 uuid                 |  name   
----+--------------------------------------+---------
  1 | f9b0de79-3f38-4cbb-9275-6d68b472e943 | Alice
  2 | 13df1b18-5c6e-4879-9b2f-c78ea215c809 | Bob
  3 | 21f5e0ee-1e1b-4d8d-8768-d0acc276efd5 | Charlie


select * from test_b;
  id |                 uuid                 | idx |  location  |              traveler               
----+--------------------------------------+-----+------------+--------------------------------------
  1 | b972acfc-d5b2-4e65-bf31-0a515ee1a2f1 |   0 | Chicago    | f9b0de79-3f38-4cbb-9275-6d68b472e943
  2 | 676143d6-abe1-48d6-bf78-c72b3186ede3 |   0 | Pittsburgh | 13df1b18-5c6e-4879-9b2f-c78ea215c809
  3 | b0cb5e4f-3070-4403-89dd-6063864233c4 |   0 | Denver     | 21f5e0ee-1e1b-4d8d-8768-d0acc276efd5
  4 | b972acfc-d5b2-4e65-bf31-0a515ee1a2f1 |   1 | Charlotte  | f9b0de79-3f38-4cbb-9275-6d68b472e943
  5 | 676143d6-abe1-48d6-bf78-c72b3186ede3 |   1 | Las Vegas  | aeae809f-13df1b18-5c6e-4879-9b2f-c78ea215c809
  6 | b0cb5e4f-3070-4403-89dd-6063864233c4 |   1 | New York   | 21f5e0ee-1e1b-4d8d-8768-d0acc276efd5

select * from test_c;
 id |                 uuid                 |              traveler               |      log       
----+--------------------------------------+--------------------------------------+----------------
  1 | 181b6f69-ea7b-4e2f-90c7-89999168f2aa | f9b0de79-3f38-4cbb-9275-6d68b472e943 | Had fun
  2 | 5770b1f1-b502-4ef2-9443-9075734a7585 | 13df1b18-5c6e-4879-9b2f-c78ea215c809 | Been better
  3 | 32bb94ba-76bb-4d89-8da3-c5ef7a4957b5 | f9b0de79-3f38-4cbb-9275-6d68b472e943 | Had fun
  4 | a257bb49-078f-4f03-a430-f331aee86da1 | 21f5e0ee-1e1b-4d8d-8768-d0acc276efd5 | Going to leave
  5 | a1b3d545-d4b8-462b-a93b-e87ba9fce849 | 21f5e0ee-1e1b-4d8d-8768-d0acc276efd5 | Missed home
  6 | f2d67010-6948-49a3-b401-3005812aaca6 | 13df1b18-5c6e-4879-9b2f-c78ea215c809 | Great place

Let’s say table test_a is a trip for a traveler, test_b is a list of the cities they went to. Example Alice has traveled twice to Chicago and Charlotte. Along the way they keep a log of their trip in test_c. What I want is to add the uuid and idx from test_b to test_c assuming the order in test_b matches the order found in test_c.

Answer

step-by-step demo:db<>fiddle

Joining the records from table_b on table_c needs a common join condition.

Assuming, the ids are correctly increasing with the entrys (so a newer records has a higher id), they can be used as order criterion. If not, you’ll need one, because there’s nothing like an insert order. Inserting a record does not ensure any query order.

Well, to create the common join condition, you can use the row_number() window function, which creates a row count within an ordered group (= partition). In your case, the partition is the traveler, the order is the id (here table_b):

     SELECT 
         *,
         row_number() OVER (PARTITION BY traveler ORDER BY id)
     FROM table_b;

which yields (notice the last column, which was created)

> id | uuid                                 | idx | location   | traveler                                      | row_number
> -: | :----------------------------------- | --: | :--------- | :-------------------------------------------- | ---------:
>  2 | 676143d6-abe1-48d6-bf78-c72b3186ede3 |   0 | Pittsburgh | 13df1b18-5c6e-4879-9b2f-c78ea215c809          |          1
>  3 | b0cb5e4f-3070-4403-89dd-6063864233c4 |   0 | Denver     | 21f5e0ee-1e1b-4d8d-8768-d0acc276efd5          |          1
>  6 | b0cb5e4f-3070-4403-89dd-6063864233c4 |   1 | New York   | 21f5e0ee-1e1b-4d8d-8768-d0acc276efd5          |          2
>  5 | 676143d6-abe1-48d6-bf78-c72b3186ede3 |   1 | Las Vegas  | aeae809f-13df1b18-5c6e-4879-9b2f-c78ea215c809 |          1
>  1 | b972acfc-d5b2-4e65-bf31-0a515ee1a2f1 |   0 | Chicago    | f9b0de79-3f38-4cbb-9275-6d68b472e943          |          1
>  4 | b972acfc-d5b2-4e65-bf31-0a515ee1a2f1 |   1 | Charlotte  | f9b0de79-3f38-4cbb-9275-6d68b472e943          |          2

The same can be done with table_c.

Now, you created a normalizing row count on every traveler‘s records. Because the record numbers equal (which you presumed in your question), this is your join condition:

SELECT
    *
FROM (
    SELECT                                                       -- 1
        *,
        row_number() OVER (PARTITION BY traveler ORDER BY id)
    FROM table_c
) c LEFT JOIN (
    SELECT
        *,
        row_number() OVER (PARTITION BY traveler ORDER BY id)    -- 2
    FROM table_b
) b ON b.traveler = c.traveler AND b.row_number = c.row_number   -- 3

1. table_c with row counts
2. table_b with row counts
3. Join on traveler and row count

Well, if you want to add the table_b‘s columns uuid and idx to table_c, you need new columns:

ALTER TABLE table_c 
ADD COLUMN b_uuid text,
ADD COLUMN b_idx int;

And then you have to fill them using an UPDATE statement, which uses the query above:

UPDATE table_c c
SET b_uuid = s.b_uuid, b_idx = s.b_idx
FROM (
    SELECT
        c.id,
        b.uuid as b_uuid,
        b.idx as b_idx
    FROM (
        SELECT 
            *,
            row_number() OVER (PARTITION BY traveler ORDER BY id)
        FROM table_c
    ) c LEFT JOIN (
        SELECT
            *,
            row_number() OVER (PARTITION BY traveler ORDER BY id)
        FROM table_b
    ) b ON b.traveler = c.traveler AND b.row_number = c.row_number
) s
WHERE s.id = c.id;