Update a table based on another table in PostgreSQL

Question

I have two tables:

products

| id | name        | price | type_id 
------------------------------------
| 1  | Product A   | 500   | 1
| 2  | Product B   | 600   | 3
| 3  | Product C   | 800   | 15

types

| id  | price | 
---------------
| 1   |       |
| 3   |       |
| 15  |       |

Now I want to set the price in the of the types table to the values in products table whose has the corresponding type_id

So in the types table 1 => 500, 3 => 600, 15 => 800 given the fact that type_id is unique within products table.

I tried to write a query like:

UPDATE types SET price = (
   SELECT sub.price FROM (SELECT p.type_id AS id, p.price AS price
                          FROM products p, types t
                          WHERE p.type_id = t.id) AS sub
   WHERE sub.id = types.id
)

However, the query does not get to work correctly and seems to be too complicated. How should I do that?

Houari · Accepted Answer · 2015-05-25 21:18:08Z

1

If a type is attributed to only one product in maximum so:

UPDATE types set price = products.price 
from products where products.type_id= types.id

answered May 25, 2015 at 21:18

Houari

5,6513 gold badges33 silver badges57 bronze badges

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

1 Comment

DucCuong Over a year ago

Yes. I works correctly in my case. I will accept your answer now :D

Collectives™ on Stack Overflow

Update a table based on another table in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related