Postgres - UPDATE table using JOINS

Question

I have a table products:

----------------------------------------------------------------------------------
id | purchase_date   | product  | product_info_join_table_id | shipment_status_id
----------------------------------------------------------------------------------
1  | '2017-01-01'    | computer | 23                         | null
2  | '2018-01-01'    | printer  | 45                         | null
3  | '2019-01-01'    | keyboard | 34                         | null

There is a product_info_join table that links the products table to other tables, such as country_to_ship table:

product_info_join:

------------------------------
id  | destination_id   
------------------------------
23  | 11                     
45  | 11                     
34  | 12

country_to_ship:

--------------------
id  | destination   
--------------------
11  | United States                     
12  | Mexico

I need to figure out what the shipment status of each item is (which is currently null). To do so, I need to look up the shipment_status table.

shipment_status

----------------------------
id  | country       | status
----------------------------
1   | United States | ship                     
2   | Mexico        | hold

So when I go to UPDATE the products table, it should look like this (notice the updated shipment_status_id column):

----------------------------------------------------------------------------------
id | purchase_date   | product  | product_info_join_table_id | shipment_status_id
----------------------------------------------------------------------------------
1  | '2017-01-01'    | computer | 23                         | 1
2  | '2018-01-01'    | printer  | 45                         | 1
3  | '2019-01-01'    | keyboard | 34                         | 2

So I am trying to UPDATE the products table by joining it with the product_info_join and country_to_ship to get the destination column. I then want to use the desitnation column to look up the shipment_status table to fill the shipment_status_id column (which indicates whether or not to ship the item).

WITH cte_shipment_status AS (
    SELECT *
    FROM shipment_status
    WHERE country IN ('United States', 'Mexico')
)
UPDATE products
SET shipment_status_id = (SELECT id FROM cte_shipment_status WHERE country = destination)
    LEFT JOIN product_info_join ON products.product_info_join_table_id = product_info_join.id
    LEFT JOIN country_to_ship ON product_info_join.destination_id = country_to_ship.id;

This doesn't work because you can't use LEFT JOIN as I have. I know you are supposed to use a FROM clause to do JOIN's with UPDATE, but I don't know how I can use FROM when I am trying to join two tables.

Gordon Linoff · Accepted Answer · 2021-03-07 15:25:21Z

1

You can express the logic like this:

update products p
    set shipment_status_id = ss.id
    from product_info_join pij join
         country_to_ship cs
         on pij.destination_id = cs.id join
         shipment_status ss
         on ss.country = cs.destination
    where pij.id = p.product_info_join_table_id and
          cs.country in ('United States', 'Mexico');

This really assumes that there is only match in the underlying tables.

answered Mar 7, 2021 at 15:25

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres - UPDATE table using JOINS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related