CODEWITHSUNDEEP
sqlpostgresqlsubquery
DucCuong
DucCuong

Reputation: 648

Update a table based on another table in PostgreSQL

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?

Upvotes: 0

Views: 64

Answers (1)

Houari
Houari

Reputation: 5631

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

Upvotes: 1

Related Questions