Using Postgres, I'm trying to insert into table "sellable_product_categories" ids coming from two different tables (sellables & product_categories).
The first table has multiple values while the second only has one (the id of the product category).
insert into sellable_product_categories (sellable_id, product_category_id)
values (
(select id from sellables where category = 'Backsplash'),
(select id from product_categories where name = 'Tile'));
The code generates this error: "ERROR: more than one row returned by a subquery used as an expression"
which makes sense since the insert is asymmetrical, but how to resolve this?
The desired result would be something like this:
id | sellable_id | product_category_id |
---|---|---|
1 | 17 | 8 |
2 | 20 | 8 |
3 | 35 | 8 |
4 | 64 | 8 |
5 | 89 | 8 |
6 | 92 | 8 |
I tried many different permutations, including a left join, but at this point I'm just flat out stumped. Any help would be greatly appreciated.
CodePudding user response:
You can insert the appropriate values directly from a query where you match up the sellables and the product category as required. It appears from your question that you want a cross join on a subset of rows from each table, which can be done like so:
INSERT INTO sellable_product_categories (sellable_id, product_category_id)
SELECT s.id, p.id
FROM sellables s
CROSS JOIN product_categories p
WHERE s.category = 'Backsplash'
AND p.name = 'Tile'